Prev: Append a current record in a subform to another table
Next: Migrating to 64 bit access db solution
From: Sanford on 20 Jan 2010 17:19
Hi everyone,

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
the application.

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.

Thanks
From: Rich P on 20 Jan 2010 17:47
greetings,

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.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
From: Salad on 20 Jan 2010 18:44
Sanford wrote:

> Hi everyone,
>
> 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
> the application.
>
> 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.
>
> Thanks

Why not look at AutoFE (easily googled)?
From: Sanford on 20 Jan 2010 21:58
Thanks Rich and Salad for the input, really appreciated.

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.

From: Sanford on 21 Jan 2010 12:53
I tried using a DAO database connection instead of bound forms, and am
having the exact same performance issues.

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
 |  Next  |  Last
Pages: 1 2
Prev: Append a current record in a subform to another table
Next: Migrating to 64 bit access db solution