I was recently handed a CD that contains an Access database. The person who created the CD appears to have just copied and pasted the database folders and files on to the CD.
When I try to view the database forms, I keep getting an error saying:
And indeed, it isn’t a valid path. I am not running the database from J:\ (which is obviously where the person who copied the files on the CD does), I am running off my local C:\ (but with an otherwise identical path structure).
How can I tell this MDB application to look in C:\ for the data instead of J:?
Is somefile.mdb (the one you get the message about) the same file that you were trying to view the database forms in, or a different file?
If they’re different files, then the one file has one or more references to the second file using that J:\ absolute path. However, I think that there are a lot of different ways that a reference like that could work - it could be inside the design of a query, or the properties of a form or report, for example.
But I think one possibility is that the references are in ‘table links’ - you have one table from a particular access file and you link it into another access .mdb file so that it can be used as if it were a local table.
I built an access app like this myself once upon a time, and I remember including an access VBA macro that could be used to ‘repoint’ all the links to a new path when that was necessary. I’ll look for that and post the code here, because it might be a help to you.
I’ll also take a look to see what icon is used for linked tables in access 2003, if you look at the list of all tables. That may help us determine if it’s linked tables at all.
Oh - and if it is the same filename that it expects to be on J drive, then I’m not sure what’s going on - some kind of self-reference maybe, but that seems crazy.
somefile.mdb is the database and tables. Someapp.mdb is the application, but as soon as you try and do anything in the app that requires accessing the data (somefile.mdb), the error gets thrown.
I don’t think the issue is with linked tables… I have tried tinkering with that stuff already.
Okay. Well, if I had access to the files, I’d probably be troubleshooting by working my way up the levels of database abstraction:
Are there any tables listed at all? If not, then obviously the queries/forms have a direct reference to somefile.mdb If there are, try opening them up in data view to see if the tables are local (open fine) or linked (they’ll complain about J drive)
Are there any queries? If there are, open them first in design view, and then use the toolbar to switch to SQL view, because that will let you see the paths quite clearly if the queries refer to somefile.mdb . You should be able to update the paths in SQL view to the current location of somefile.mdb and save them.
Next, in looking at the forms themselves, if you haven’t found references yet, try opening each form in design view again, accessing the form properties, and looking at the ‘record source’ property - another likely place where there might be a pathname reference.
Also, if the form has the ‘has module’ property set to yes, then you can go into the VBA editor and look at the module code, to see if it references j:\somefile.mdb , and change it there. Same goes for any modules that aren’t tied to a form.
If there are any reports or data access pages, you can check their record sources like you did for forms.
I’m not sure about old-style, non-VBA macros, it’s been a long time since I’ve worked with them and I’m not sure which ones might reference external paths. You should be able to scroll through the list with the arrow key and look at the properties of each macro step in turn, though.
Good luck, I’m exhausted just telling you about it.
If your J: drive is not currently being used, you can try this trick:
Open a DOS prompt and enter:
subst J: C:\
This should work if you copied the *.MDB files to C:\somepath\somedb\
This creates a “fake” J: drive, and the code inside someapp.mdb doesn’t have to be modified.
Do not do this:
subst J: C:\somepath\somedb\
If you don’t do the SUBST trick, you’ll have to open up the MDB file and do a search & replace on all the Modules and Forms with code. Ultimately, this would be the better approach but it takes more effort.
Ruminator, thank for the tip, but unfortunately J:\ is a network drive for me.
chrisk, thank you for your excellently detailed suggestions. I was finally able to get it working by somehow opening up what was called something along the lines of Link Table Properties from within someapp.mdb, where I was then able to select “prompt for new path” (or something similar). I was then able to navigate to somefile.mdb, and now it works.
