Splitting your Microsoft Access MDB into a front end and back end - Details

[ Main | AccessSplitting ]

(Updated 2010-09-27)

In a controlled environment, i.e. your own organization, you can use either a fixed drive letter and path, Z:\Path\data.mdb, or the UNC and path, \\server name\path\data.mdb. You can set this up on your own system and so long as everyone has the same network drive letter or sees the same server you can distribute your FE as is.

If you're dealing in a non controlled environment then you'd want to see if a particular table can be opened. If fine then exit. If not then locate a previously stored path, either in an INI file as I do, a registry entry or otherwise, and then relink the tables. See the Access Web Relinking Tables.  Use the API call as linked in that posting instead of using the Common Dialog control as you will have lots of version problems on other systems.

If that previously stored path no longer works or doesn't exist then use the File Open API calls to locate the backend end. Once successfully located store this location as previously mentioned so the users won't have to find the location in the future. Especially if you give them a new front end. Do not store this location in either the FE or the BE as these won't be available the next time you need them. I prefer using an INI file as that is easy to locate and understand if you ever have to update it manually.

Note that refreshing links to tables can be dramatically sped up. Even in Access 97. Once the first table has been refreshed open a recordset based on that table. Continue refreshing. Once finished refreshing close the recordset after you've opened a bound form for performance reasons.

Michael Kaplan has posted the following:

"One thing you can look at is the deletion/recreation of links. I have been amazed at how much stuff gets cached in the links for tables, and also how much of what is cached is based on usage patterns (so that if the two users make use of a link two different ways, the info might be different). Deleting and recreating the link can often resolve these weird cases.

The interesting "features" (bugs) in linked tables that have to do with the way that they are implemented from a low level architectural standpoint is something I have thought about doing an article on, but its hard to really imagine that it would be something the world would want to read about (and I get criticized often for not writing enough "real world" stuff)."

Thus I'd suggest that if you rename fields in tables or insert new fields in the middle of a table, rather than at the end, that you recreate the links rather than refreshing.

For developers

At one client I use a variation of the drive letter approach and don't bother to relink the tables. I have two three line batch/cmd files. In one I use the SUBST command to create a drive letter which is the same as the server drive letter. In the other I use the NET USE command to link to the drive letter on the server. In both cmd files I delete the SUBST and the NET USE first.

I then copy the clients live BE data MDB to my system every day or two to ensure I'm working with a reasonably current copy of their data. I can then do any testing I want on my own system without affecting the live BE.   This includes accidentally deleting the entire contents of one transaction table when I forgot to put a WHERE clause on a DELETE query.  <smile>  I run the above cmd files to switch between using the live BE and my copy of it. If I need to update the backend's tables, fields and/or relationships I wait until the end of the day when everyone is out of the system. Once added I copy it down again.

I also have an always open form in the corner which, for me only, is pale yellow if the BE is on a local hard drive. However if the BE is on a network drive this form becomes the reddest possible value. This makes an excellent visual warning for me to be very careful what I do. <smile> I used the code at How To Determine the Type of Drive Using Win32 - 161300.


[ Splitting | AccessMain ]

Auto FE Updater   Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Wrench and gear Granite Fleet Manager - the best designed fleet maintenance tracking and management system available

Comments email Tony  Search Contact Tony's Blog Privacy Policy Table of Contents

Website copyright 1995-2013 Tony Toews