How to change embedded file name

[ronb]

I know nothing about Access, but have an issue a church with an .mdb file someone previously created.

A Excel file( .xls ) is created by another program and that file's absolute location is embedded in an .mdb file. The data from the Excel file is used to generate attendance sheets using the .mdb file and a "trial" version of Access. The software that creates the Excel file was upgraded and the new software is in a different location that previously. The report that creates the Excel file works in the new location, but the location where the file is saved is under the new software's directory.

I would like to modify the location of the Excel file that is in the .mdb file to point to the new location.



How do I do this?

It appears that the computer this .mdb file is on only has a "trial" version of Access. However, I can use Access on another machine to modify the file location in the .mdb file if necessary. BTW this is running on Windows XP. Other machine with Access is running Windows 7.

[June7]

Depends where the Excel file pathing is embedded. If it is a VBA procedure, do a search in the VBA editor for .xls.

If it is stored in a table and code queries the table for the path, change the string in the table.

If the path is in a macro somewhere, good luck finding it. I don't use macros and don't think can do a search of macro code with the Access Find/Replace tool.

You can get third party add-in software that can search VBA and macro code (as well as expressions in queries, forms, reports and properties of objects and controls). I use Rick Fisher's Access Find and Replace. Cost about $50 (does NOT search data in tables).

[ronb]

Thanks for the reply June7! But, I'm still having a little trouble wrapping my head around how all you mentioned fits together. Additional comments are appreciated.

When I double click on the attendancereport.mdb file Access( trial version ) opens.

On the left side of the screen is a list of what appears to be items from the .mdb file.

One of those items is labeled qmf_tmp.

When I mouse over this item a popup appears showing the path to this object.

So I have been assuming this path is embedded in the .mdb file somewhere and that is what I want to change.

Does this additional explanation help identify where the path to this item may be located in the .mdb file and how I can go about changing it? Or is your answer still the same?

Thx in advance.

[June7]

Is that item a table with a little arrow icon at the left? That signifies a linked table. This mdb is linking to data tables in another file, perhaps another mdb. This is a split design. What is the full path and filename showing in the popup?

Can use the Linked Table Manager on the Database Tools tab to modify the links.

It is possible for Access to link to Excel spreadsheets but I am not clear if that is what your project is doing. If you want to provide db for analysis, follow instructions at bottom of my post.

Sounds like you could benefit from some basic introductory tutorials. Get a good beginner's reference and spend a day working through exercises.

[ronb]

Yes, this item has a small arrow next to it pointing to the right. The icon for the file is the Excel spreadsheet icon.
Path = C:\PowerChurch\PowerChurch9\qmf_tmp.xls

Tutorials is a good suggestion, but I'm hoping this is the only time I'll have to deal with Access.

Thanks

[ronb]

Thanks June7

I was able to use Access 2010 tonight and load the .mdb file. Then I was able to find the linked table manager.

No time to fix the file name tonight, but I will be able to tomorrow.

I can't tell you how happy I am with the help you have provided me.

Ron