When the symptoms encountered indicate that performance is acceptable with a single user in the database but drops significantly when two or more users are in the database, the problem may be caused by interaction with the LDB file.
In Access 2000, when a second and subsequent user tries to access a shared backend database on the server, there seems to be a situation where Access tries to perform a delete on the LDB file (which fails because another user is currently in the file). This attempt is made about 15 times before silently failing and the records are returned from the linked table.
To resolve this issue we need a persistent connection to the back-end from each of the front-end workstations. This can be done using a bound form which is always open or by keeping a recordset open at all times..
A good way of testing this is to ensure you are the only one opening the back end. Then run the front end database until it gets to the main menu. At this point you should see an LDB file present on the server with the same name as the back end.
If you don't see this LDB file then you know you don't have a persistent connection.
Bound form
The simple situation would be to create a simple form based on a table. Bound form means that the record source of the form ha a table or query specified. Any table but one with as few records as possible. Or create a dummy table and put one record in it. In your startup form add the following line of code in the startup forms OnOpen event.
DoCmd.OpenForm "frmKeepOpen", acNormal, , , , acHidden
I always have a global options table containing such details as default corporate logo, report banding true/false and colour, bar code font file name and so forth. I have a hidden form bound to this table which is always open. I then reference these fields as appropriate in code using forms!GlobalOptions!goReportBandingColour and forms!GlobalOptions!goCorporateLogoPathandFilename.
Global Recordset which is always open
Alternatively you can create a dummy (test) table in the backend file and create code in the front-end file which opens a recordset on this table and persist the recordset until the front-end app is closed. To do so:
Create an empty form. .
Declare a recordset variable in the global declarations section.
In the OnOpen event open a recordset against any table.
In the OnClose event, which will fire when the MDB is closed, close the recordset and Set variable to nothing
Ensure you always open this form when opening the MDB. You will likely want to open this form hidden.
Maintaining persistent connections to linked tables could improve performance significantly because it prevents Microsoft Jet from constantly deleting, creating, and obtaining locking information from the other database's locking information file.
Public rsAlwaysOpen As Recordset
Private Sub Form_Close()
rsAlwaysOpen.Close
Set rsAlwaysOpen = Nothing
End Sub
Private Sub Form_Open(Cancel As Integer)
Set rsAlwaysOpen = CurrentDb.OpenRecordset("DummyTable")
End Sub
Global database connection which is always open
David Fenton suggests using a global database connection which is always open. Same concept as using a table in the above section.
Dim dbsAlwaysOpen As DAO.Database
Private Sub Form_Close()
Set dbsAlwaysOpen = Nothing
End Sub
Private Sub Form_Open(Cancel As Integer)
Set dbsAlwaysOpen = OpenDatabase("Q:\1 access\test 2000 BE.mdb", False)
End Sub
The problem with this approach is that you need to know the path and name of the backend MDB. While you can use a parsed linked table connection property this becomes more work.
