Keep Backend Table Open 1

[mkallover]

I was reading an article a few weeks ago that when you have a split database it can help performance if you keep tables open at all times using a hidden form. At the time I was bogged down in another project so I couldn't get into it much and of course, I forgot to bookmark the article and now I can't find it.

Does anyone have a good source for information on doing that or know what I am talking about?

 

[MajP]

I apologize, I do not have the link but a copy of an article. Here is the answer:

Microsoft Access Performance FAQ - LDB locking which a persistent recordset connection fixes

(Last updated 2008/01/05)

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.

 

[mkallover]

That's exactly the article, thanks a ton!