[AccessD] Persistent Connection question

[Dan Waters]

Hi Janet,

I do the same thing with one important difference.

The variable dbsAlwaysOpen should be Static.

        Static dbsAlwaysOpen As DAO.Database

This maintains the connection to the back end data tables.

I have tested this and your users will definitely notice an improvement.
The reason is that it takes time for Access to open connections to the data
tables on the server - maintaining a Static variable to that database means
that Access only needs to establish the connection once, not every time it
wants to do any CRUD operation.

I think that Jim's suggestion to open a recordset will do the same thing, as
long as a Static recordset variable is used.

Dan

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Janet Erbach
Sent: Friday, April 03, 2015 3:41 PM
To: Database Advisors
Subject: [AccessD] Persistent Connection question

Hello all -

I've finally discovered what's been causing extremely flaky behavior in a
database I've been wrestling with for the past 3 weeks.  It was one line of
code in the menu form that opens with the app:

Dim dbsAlwaysOpen As DAO.Database
Set dbsAlwaysOpen =
OpenDatabase("S:\MAINT\ToolingTech\dbToolingTechData.accdb", False)

Some forms were not populating consistently, records were sometimes being
dropped during select and insert queries, other forms needed to be manually
refreshed repeatedly in order to display all records, things like that.
The app worked fine when opened in design view - not when run from the menu.

This morning I bound the menu form to a small, 'fake' table in the backend
and everything seems back to normal now.

Why would this method of establishing a persistent connection cause issues?
I found code like this on a couple of websites when I was pursuing serious
speed issues with the app.

Thanks!

Janet Erbach
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com