Results 1 to 8 of 8
  1. #1
    crobaseball is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    128

    Excel import link to Access is broken; data won't update

    Hello,

    I have a workbook which has links to access to import data. I have to have the information in Excel for a couple reasons: 1.) Many of our customers don't have Access. 2.) The customers use a "discount calculator" to apply their own discounts. They also use it to then apply their own margin mark ups to be able to then use to quote to their customers. So a static report/pdf for them to look at doesn't work.

    I've begun to create links from a number of queries in Access, which work fine. My problem is when I go to update the data. I'm not getting the following error coming up:



    The database definitely hasn't moved. And I've run the queries in q, and there aren't any problems there. I've looked this error up and I'm seeing that it could mean that I've got some corruption going on.



    Before I hit my main q, I should also note: I'm running all of this on a Citrix network. I should also note that it's a consulting gig. Once I'm done, I won't be available to fix major issues like this. My main q: will this corruption problem/error happen frequently? I'm in the early stages of linking this data, and I'm having a hard time fixing it, and the people who are there will really have trouble doing so. Anyone out there have experience here?

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    How are the customers accessing their data, is it through a website?
    do they have an available VPN connection to your network?
    How many concurrent users do you have?
    does the linked data allow them to update any of your data?
    Is the function to update the data in the excel workbook or on the access side (it sounds like you have a vba function in excel to refresh the data but I can't tell for sure)?

  3. #3
    crobaseball is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    128
    They access the data through Excel.

    They do not have a connection to our network, nor do we want them to. I want the information to be updated/refreshed by us, then we load the "static" Excel workbook on to our website for them to then use.


    No concurrent users.

    Linked data is Read Only . . . tho I would love to be able to use data from Excel to be used as the filter parameter; however, I'm afraid I'll have to write some extensive VBA code to replicate the Import links I can create.

    On the Excel data. And I don't have a vba function, I just hit Refresh ALL.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if they don't have a connection to your network no you to theirs, linked data wouldn't work at all.

    Are you using the method

    docmd.transferspreadsheet acExport when you 'refresh' your excel files? if so you're just exporting data, not linking it.

    Maybe an example of the code you're running and a more detailed description of what you're doing would help because you seem to be saying contradictory things in your post that I can't reconcile.

  5. #5
    crobaseball is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    128
    I'm actually not using code, but simply using the import function of Excel. I keep the link active to update the workbooks when data changes in Access. When it changes, I then upload the workbooks onto the company website for customers to then download. So the active links to Access work on our back end side, but the data is then static once it is actually used by our customers.

    Does this make more sense?

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Ok, so if you are not relying on any data coming back from the customers, you are simply exporting (I'm viewing this from access, NOT the excel side) data from Access to Excel can you not just perform an export from the Access side to avoid the error message you're getting? You can write VBA to perform any number of exports (if you're dumping a query that's very simple) instead of involving Excel as anything other than a data destination. If you have 100 queries that you 'importing' into excel then copying to another location for the customer to pick up, my point is you can do this directly from Access.

  7. #7
    crobaseball is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    128
    I've actually decided to take different tack. BC of the sheer number of queries I would have to build, I've decided instead to build a general query that then uses some values in Excel to act as parameters to filter the populating data. So 3 queries, rather than a couple thousand . . . but more work in Excel to make everything format correctly. I'm sure I"ll make some errors and will need help along the way, lol

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You can do that automatically with MS access as well. For instance if you have 20 customers and you are exporting the same query for all 20 of them, just supplying the Customer ID as the criteria for each iteration of the query to differentiate the sets of data. You can use VBA to do this.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Link to Excel; number of records change and Excel can't update
    By crobaseball in forum Import/Export Data
    Replies: 5
    Last Post: 03-22-2014, 11:40 PM
  2. Macro to mimic Import & Link Excel button on external data
    By arnstrb in forum Import/Export Data
    Replies: 1
    Last Post: 03-16-2014, 07:52 PM
  3. Import access query data into excel file, using excel vba
    By exceljockey in forum Import/Export Data
    Replies: 3
    Last Post: 11-15-2013, 02:36 PM
  4. Trying to import data into access from excel
    By Cupps256 in forum Access
    Replies: 10
    Last Post: 03-09-2013, 02:31 AM
  5. Replies: 2
    Last Post: 12-26-2012, 02:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums