Solved

How do I find the location of a split database's data files in MS Access?

Posted on 2014-12-01
6
63 Views
Last Modified: 2015-08-06
We moved the front end to a different server... and can't find the data. How can I find where it used to be located so that I can import them or link them back to the application?
0
Comment
Question by:joelmcm
6 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40473931
generally, if you open the front end in design view, you can mouse over the tables and display the table name and path.  Theoretically, that should tell you were the backend is residing.
1
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40473932
Go to design mode of a linked table. Select Properties to see the connection string. That will show where the backend was.

/gustav
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40473946
You can also use the connection property to get the connection string of linked tables in the immediate window.

?currentdb.tabledefs("linkedTableName").connect
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 12

Expert Comment

by:pdebaets
ID: 40474229
I would not recommend importing the tables once you find them. It would be best to continue to have a split database for reasons too numerous to list here.

Once you find where the data is, consider mapping a drive letter to that location (drive Z:, Y:, X: or whatever), then link to the backend through that mapped drive. Some would say that you should map to the server name (such as "\\MyServer\whatever") and this may work well until the server name changes. I prefer to use drive mapping.

For more, see: http://deepintoaccess.blogspot.com/2011/12/map-network-drives-with-vbscript.html
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40474270
You can also open the MSysObjects table to view the connection strings.  Or, just use this query:

SELECT MSysObjects.Database, MSysObjects.Name, MSysObjects.ForeignName, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=6));

@pdebaets,
The reason people suggest using UNC notation is because in many organizations, it is difficult to find a common drive letter that is available to ALL users once you get more than about 10 users.  And although servers do sometimes change names, it doesn't happen often.
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 40474400
@pat, yes, it can be hard to find a common drive letter and often brings up the organizational issue of standardizing mapped drives which I've implemented at a number of sites. The vbscript example I linked to above comes in handy for these situations.

If such organizational issues can't be resolved, there's a tool I've developed called TableLinker Lite that allows the developer to specify multiple possible locations for the data backend. Linking is attempted for the backends in a list and the first one found is linked to. Details are at: http://www.peterssoftware.com/tll.htm
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question