Moving the database
March 30, 2010 1:38 PM   Subscribe

How can I move an MS Access file to another PC?

At my job somebody set up an Access 2003 database back in 2003 using the MSDE database engine for an .adp(access database project) file on an XP machine. Nobody knows who the somebody is.
I know the .adp file is the front end/metadata file and it works in conjuction with a .dat file. I assume that all the user input is stored here.

What I have done
1. Copied the .adp file and opened on another PC and got a [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied error.

2. Installed MSDE engine on new PC, did a backup of the database on the old PC(which created the .dat file), did a restore on the new PC, opened the .adp file on got the following:
FILENAME could not find the database on the server. Verify that the database name is correct.

I feel like I have all the pieces, but don't know how to put them together. Step 2 was accomplished after doing the Google.
The account opening the file is a local admin, so permissioning should not be the issue(?).
Any help is appreciated!
posted by MrMulan to Computers & Internet (3 answers total)
It sounds like there is a SQL database that isn't making the transition. If you go to Control Panel » Administrative Tools » Data Sources (ODBC) you might find a data repository there that is referenced in your Access file. It is also possible that the author installed a non-native version of SQL on the original XP machine and bound it to the Access file.

Another issue that shows up with Access DBs is that in some cases you have to install modules for Visual Basic to use. This might be the case here if the file is able to reach the data but doesn't have the module installed required to interact with it. On the original machine you should open the file and subsequently the Visual Basic editor and then choose Tools » References. The result is a list of modules VB is loading. Take note of which modules are checked and make sure they are checked in your destination machine's install. I've worked on a couple projects where I had to load third party modules, so if there is one that you can't find in your destination machine you'll have to hunt for it from another source or figure out how to transfer it from your original machine.

Sorry for the lack of information, but my experience with Access is that you just piecemeal technologies until you get something to work. The end result is a web of technocrap that is usually really hard to navigate even in first-person mode. Hopefully if nothing else I've given you additional avenues to explore. Good luck.
posted by Gainesvillain at 2:35 PM on March 30, 2010

Have you tried copying the .adp file along with the ORIGINAL .dat file, to parallel locations on the new PC?

I'm guessing there's an internal link in the .adp file which specifically connects it to THAT .dat file, as they were likely created simultaneously by Access.
posted by Galen at 4:58 PM on March 30, 2010

I may not be understanding the problem since I haven't worked with these specific Access file types, but it sounds vaguely like you've got external data sources and you're not re-matching the copy to that source. In the original database these will be indicated on the table list by the normal table icon plus a small arrow pointing to the table. In some versions of Access, hovering your cursor over the icon will tell you what the source is and where it is located. If that doesn't work to pull up the information, my next guess would be right-clicking and pulling up the link's properties.

If this is the case [that your front end hads links instead of purely internal tables], once you have copied/moved the front end, the links will need to be "refreshed" to correctly access the data source. You can do this by pulling up the "Linked Table Manager" dialogue box. In the 2007 version you do this by going back to the table list, right-clicking any table, and selecting the "linked table manager" option. Not sure if it's the same process in 2003; if not, try the main menu bar. The linked table manager's pretty easy to use: click all the tables that need re-pointing, click the "always prompt for new location" box, then OK to browse to the data source.

If the database is set up to open in a way that never lets you see the table list (and other helpful db tools), try holding down the shift key when you open the database. Your finger must be on the shift key before as well as throughout the databases opening sequence. If you take your finger off the shift key before the database is completely open, or did not hold it down early enough, you will default to whatever normally opens, rather than getting to the database window.
posted by Ys at 8:00 PM on March 30, 2010

« Older Less Ovaltine, please :(   |   Because apples just leave me feeling hungry. Newer »
This thread is closed to new comments.