Problem in linking ACCESS-EXCEL

[malaydash]

I have an Access & Excel linked files. I have my data in Access & do some calculation in excel. The system works fine till I change the location of both the file. Whenever I shift the files to another location or another computer the linking failed. I have tried to change the path from within excel but something is coming on the way & I am unable to refresh the data in excel. I am really stuck with the problem. Can someone tell me what should I do when I shift the two files to another location/computer.

Thanks & Regards

 

[Mr. B]

Use the Linked Table Manager in Access to refresh the link to your Excel file.

 

[malaydash]

Thanks Mr B
I applied your suggestion & the problem was solved partly. My Access file in the new location was refreshed but the Excel file is getting data from the Access file in the original location. Whatever changes I made to the Access file in new location is not reflected in the excel file. I then tried to change the connection properties (within Excel) to link to the Access file in new location. But strangely some of the queries in Access file is not showing during the process. I was able to link to one of the queries & the data immediately got refreshed giving the desired result. However I am unable to do the same for the other queries. I am unable to understand why some of the queries are showing & some are not whilst all of them are present in the Access file. Please help.

Thanks & Regards

 

[malaydash]

Can someone help me on this as I need to move my files to another PC & stuck with this problem. Please help.

Thanks & Regards

 

[DavidAtWork]

Are you saying you're updating data in a spreadsheet from Access, if so how is your Excel file getting data from Access, are you doing this through vba by setting a connection to your database, if so then why can you not update the connection parameters to the new database location.
If you're only using Excel as a data source for a table in Access, then all you need to do is move both files to new location, open the database in the NEW location and use the Linked Table Manager to relink table to the excel file in the NEW location, tick the box 'Always prompt for new location', select the table you want to relink and navigate to the new excel file
David

 

[vbaInet]

To solve your problem you should stop moving the Access database files at will. If you're using Access 2007 or Access 2010 those locations will need to be trusted so you will have difficulty getting those locations set as Trusted Locations if you keep moving the database files.

You will find the AutoFE Updater tool by MVP Tony Toews useful:

http://autofeupdater.com/

 

[malaydash]

Are you saying you're updating data in a spreadsheet from Access, if so how is your Excel file getting data from Access, are you doing this through vba by setting a connection to your database, if so then why can you not update the connection parameters to the new database location.
If you're only using Excel as a data source for a table in Access, then all you need to do is move both files to new location, open the database in the NEW location and use the Linked Table Manager to relink table to the excel file in the NEW location, tick the box 'Always prompt for new location', select the table you want to relink and navigate to the new excel file
David

Thanks David for replying. Here I am giving the details of my problem.

1. I have an Access file to keep all the data. This is my master file & it contains multiple number of tables & Queries. All the data resides in this file so you can say this is my master file.

2. Now to do some calculation I take the help of Excel. To do this I import the data of one of my Access query (Lets say Access_Query_1) to Excel & placed this as a table in Excel (Lets say Excel_Table_1). I then do some calculation in excel & get some result in the same table. This table is then linked to my Access Database as a linked table.

3. I then use the above data of Excel_Table_1 to build another two queries (say Access_Query_2 & Access_Query_3). Then I import the data of these Access queries to Excel & placed these as two table in Excel ( say Excel_Table_2 & Excel_Table_3). I then do some calculation in excel & get some result in these tables. These tables are then again linked to my Access Database as a linked tables.

4. This solves my purpose until I change the location. The moment I change the location the whole system stops working. I have tried the method suggested above but not able to solve the problem. The details of the problem I have explained in my earlier post dated:22/01/2012.

All my work is done in Access. I only use the excel to do some calculation which I was unable to perform in Access as my knowledge in VBA is very poor. As per as Excel is concerned I just open it & then close it to refresh the data.

And I would also like to know How the excel files can be refreshed from within Access. At present I have set in the connection properties of Excel as to Refresh the file every time it opens. But I have to manually open the excel file to refresh the data. Is there a way so that I can refresh the linked excel data within access as I am using access as my master file.

Thanks & Regards

 

[malaydash]

To solve your problem you should stop moving the Access database files at will. If you're using Access 2007 or Access 2010 those locations will need to be trusted so you will have difficulty getting those locations set as Trusted Locations if you keep moving the database files.

You will find the AutoFE Updater tool by MVP Tony Toews useful:

http://autofeupdater.com/

Thanks for your response

But I need to move those files to different computers as I have to distribute it to different users & they are not connected to each other through any network. Hope you understand my problem.

Any help please

Thanks & Regards

 

[DavidAtWork]

Would be easy if you could store all the output to Excel files on a network as then you would have a fixed location.
For the situation you have I can only suggest that you set up a folder structure on all PC's exactly the same ie C:\Excel_Tables\ and you make Access output all queries to this folder, then relink all your tables, then you'd be in a position to copy all the output files on to each PC and then the Access file and it should work ok.
BTW why can't you do all the calculations in Access, why does it have to be done in Excel? If you could do it all in Access, it would solve all your problems
As for refreshing the Excel data, surely when you do a fresh output to Excel, it will overwrite the old file with new data, so there shouldn't be any refresh issue
David

 

[malaydash]

BTW why can't you do all the calculations in Access, why does it have to be done in Excel? If you could do it all in Access, it would solve all your problems
As for refreshing the Excel data, surely when you do a fresh output to Excel, it will overwrite the old file with new data, so there shouldn't be any refresh issue
David

Thanks David for the prompt reply

I am basically using Excel because I have some data in which I need to compare the values involving different rows which is pretty easy in Excel & quite difficult (at least for me) in Access. Of course I am working on that but for the time being I am relying on Excel.

Would be easy if you could store all the output to Excel files on a network as then you would have a fixed location.
For the situation you have I can only suggest that you set up a folder structure on all PC's exactly the same ie C:\Excel_Tables\ and you make Access output all queries to this folder, then relink all your tables, then you'd be in a position to copy all the output files on to each PC and then the Access file and it should work ok.

David

I cannot use a network location because the users are not connected to each other through any network. All the users will use the file independent of each other. And as per as the file path is concerned that seems to solve the problem but I may not be able to replicate the file path in all the computers I am planning to load the file due to various reason. Also this will be a temporary solution to a permanent problem. Because I have to do the same whenever I build a project linking Excel & Access.
Should I believe that there is no solution available to modify the file path when we change the location?

Thanks & Regards

 

[DavidAtWork]

As long as each user has all files in the one folder (can be different on each PC) you could write something in vba asking the user to specify the location folder where the excel files are stored using dialogFolderBrowse() function. The string value returned could be used to set the path in a connection string that would link the excel files dynamically. This can happen when the database opens.
But I don't see any other way, maybe it's time to redesign your whole system to solve this problem
David

 

[vbaInet]

But whatever calculations you're doing in Excel you can do it in Access so I don't see why you're using two applications to deploy one.

You need to run an AutoExec macro to re-link the tables. Also like I said before, if you're using Access 2007+ you will need to have already Trusted that location before uploading the Front End on the user's machine. Or you could use Tony Toews' tool, update the Front End and the tool will allow you connect your FE to the BE.