Solved

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

Posted on 2014-12-01
6
67 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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 37

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Format a text field as percent 12 56
Outlook Automation in Access Using "Find" 2 57
Why can't I change data in my query? 3 37
Queries: Select, then Append, then Delete 8 40
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

732 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