The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
April 2008

Split Databases-Putting Things Together Again After They've Come Apart

Back when I was grad student (unfortunately many, many moons ago), I moved five times over the course of two years. I became a reluctant expert in the art of packing and unpacking, and moving things from one place to another. One of the biggest challenges when moving into a new place was finding stuff. My typewriter was a critical tool for my work (yes I'm showing my age -- when I went to school we had typewriters, not computers), so when I moved into a new place, finding my typewriter became really important, reaching a critical point when it was time to write a paper. It was always a chore to figure out the typewriter's location in my new apartment.

Likewise in an Access application in which you've split your database into a front and a back end (see March 2008), if your data file ever moves (when your IT folks get a new server, for example), you'll find that you're faced with a dilemma similar to me finding my typewriter-how to find your data.

In this month's Wizard, we'll tackle the problem of re- linking backend tables from a split database.

In this Issue
  • Tip of the Month-Find the Source of Linked Tables
  • Symptoms that Something is Amiss
  • Relinking your tables
  • Conclusion
  • Trap of the Month-Beware of Copies of Data Files

  • Symptoms that Something is Amiss

    If for some reason your backend data file becomes unavailable (it's been moved or the network is down), when you try to open a form, report or query you'll get a message saying that the "data is not available". In some cases, depending upon how you structured your forms and reports, the message may be more obscure.

    If you have a split database and you're getting a message along the lines of "data is not available", the first diagnostic step is to make sure that your tables are properly attached.

    The easiest way to do this is to open the table list and double-click on one of your linked tables. If it opens successfully, the link is working. If you get a message saying it can't find the table, then you know the link is broken.

    In many cases this will stop your application dead in its tracks. Luckily, the solution is simple--you simply need to relink the tables.

    Relinking your tables

    Access makes the process for relinking tables pretty simple. The only prerequisite is that you know where your backend file is located. When you want to re- establish the links, it's helpful to know the location of the data file before you begin the process. If you can't find the data file, you may still move forward, but it'll be a tougher task.

    What I tend to do is to open Windows Explorer and navigate to where by data tables reside. I'll typically copy the path that shows up in the address bar in Windows Explorer. Once I have the path on my clipboard, I know that I can paste it into a dialog box when I need to.

    To relink tables, take the following steps: Click on the Tools Menu at the top of the screen, choose Database Utilities, then click on Linked Table Manager. You'll get a screen similar to the one below:

    This is where you'll see the old path to your tables and where you get to relink them to the new path. If all of your linked tables are in a single database you can click on the Select All button. Otherwise click the tables that for your first data file, then repeat the process.

    After you've selected your tables, click OK and you can browse to the location of your data file (if you pasted the link from Windows explorer as I recommend above, this will ease the process.) Once you've selected the data file, in most cases this will solve the problem. There are some nuances to aware of, though. Access will typically let you know if there is a problem with one or more tables. At that point you'll have to do some detective work. Keep in mind that you have to navigate to wherever your data file resides. If you believe the file contains your table or tables and the Wizard refuses to believe you, go ahead and open the data file to verify that the table is there and that you can open it from within the data file. If you can, make sure that the path you're putting into the Linked Table Manager is the same path that you just used to open the data file.

    If you have a copy of your data file in more than one spot (usually not a good idea, unless the copy is clearly a backup and in a folder that's marked as such), make sure that all your tables are pointing to the new location rather than the old location and also make sure that you click on the checkbox that says "always prompt for new location".

    In instances when your data files are in more than one database or where you're linking to non-access tables (a very powerful feature of Access), the process is a bit more complex and we'll cover that in a future wizard.


    Once you've finished re-linking your tables, the error messages should subside and you're ready to rock and roll with your application.

    Trap of the Month-Beware of Copies of Data Files
    Acess Traps

    One of the biggest problems I see with my customers is that they have multiple copies of their data in more than one spot. In most cases, this doesn't present a problem since what they're tried to do is to back up their data -- a very reasonable thing to do. However, in some cases users mistakenly link to back up copies of the data files rather than to the live database. This causes them to be reading and writing to one database while their colleagues are reading and writing to another database. You can imagine the problems this causes!

    In order to avoid this predicament, when you back up a database give the backup a name that clearly indicates it's not the live copy of the data. What I tend to do is to make a copy of the data file and then change the extension from mdb to an identifier indicating the dates that I'm making a backup.

    For example, I'll change the name from Invoice_be.mdb to Invoice_be.031708.

    If you take this approach it then becomes almost impossible to link to the wrong database.

    Tip of the Month-Find the Source of Linked Tables

    At some point may need to know where one of your linked tables resides. There's a very easy way to do this; simply right click on one of your linked tables and choose design view. You'll get a warning saying that the table is a linked table with some properties that can't be modified - click OK.

    Once you're in design view, right click on any white space in the view, then click on properties. One of the properties you're able to see is the description, which will contain the path to the data file as well as the name of the table that you're linking to.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Forward email

    Safe Unsubscribe
    This email was sent to jim@custom-software.biz, by jim@custom-software.biz

    Custom Software | Copyright Custom Software All Rights Reserved | Westford | MA | 01886