|
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.
| 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.
|
| Conclusion |
|
|
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 |
|
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.
|
|
