I have an application that uses a FE/BE configuration. I maintain a
connection to a local backend MDB containing temporary data and
standard lookup tables.
I do this because we have a slow internet connection between our
satellite office and the main office where our server resides; so
every time a user opens their FE, it copies a fresh version of this
local MDB to their machine from the server and then opens a hidden
bound form to keep the connection established.
Some users, designated as Administrators are able to make changes to
this local MDB. In order to do this and have the changes rolled out to
the other users, I have a routine which backs up the master copy of
the MDB on the server, then copies the local MDB to the server, which
then becomes the new master.
In order to copy the MDB to the server, I have a routine to close any
forms bound to the local MDB, including the hidden bound form which
maintains the connection to the local MDB. Once the MDB is copied to
the server, the hidden bound form is reopened.
This all *appears* to work fine, and I can see that the connection to
the local MDB has been re-established, because LDBView (Jetutils.exe)
shows the connection is open.
However, the performance of the application from this point on is as
if the local connection is not established (i.e. very poor!). The only
solution available for the user at this point is to close and reopen
Is there a way to get Access to recoginze this connection again, or is
there a proper way to re-establish this connection, as if it was never
closed in the first place? Greatly appreciate anyone's help on this.
The problem you are encountering is pretty classic. You are trying to
use a File based RDBMS (Access) like a server based RDBMS (Sql Server).
In order to perform the operations you are describing -- without the
difficulties you are encountering -- you should step up to a sql server.
You say that somewhere in the mix you have a server computer -- or is
that a workstation that is acting as a server? If it is a genuine
server computer then you should load sql server on it and use that for
your back end DB, and all the different offices can connect to one DB
instead of passing one mdb around to multiple locations -- thus
converting it into multiple mdb's.
For the scenario you describe -- with mulitple offices... -- if a sql
server is not an option, then you are on your own if you want to go the
database route. One other option would be to use Excel to collect data
and data changes and then email the Excel files back to the main office
and perform mdb updates based on the Excel files. Just a thought.
Ideally, you will want to go with the sql server DB for the backend.
*** Sent via Developersdex http://www.developersdex.com ***
Why not look at AutoFE (easily googled)?
Yes Salad, I use Tony's AutoFE updater, and it is awesome. Without it
I don't know how I would deploy my apps...
Unfortunately Rich, SQL Server is not an option for me. It would
probably take me about 3 years to get my IT department to agree to
installing it for my use, but I definately agree with you in
principle... For now I am left using MDBs.
I think I am going to try another one of Tony's suggestions to use a
DAO database connection instead- http://www.granite.ab.ca/access/performanceldblocking.htm
Will post on if that works. Thanks again.
It seems that once Access closes an MDB/LDB connection that there are
issues re-opening it in the same session.
Anyone else want to weigh in ? Thanks
There shouldn't be any difference between using a bound form and a recordset or
database connection. And reopening the connection should work. Are you defining
the connection as a global variable in a module?
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/