A persistent connection is *very helpful* in speeding up shared Access applications, including relinking tables. You can run a simple experiment, timing how long it takes to relink tables with and without a persistent connection, and you should see a vast improvement with a persistent connection present. Persistent connections prevent the file server from continually deleting and re-creating a locking database file, when there is a single user using the application. \u00a0The FE clients can re-use a connection that has been persisted, instead of having to spend the time creating a new connection each time.\u00a0 Regardless of your specific issue, I highly recommend that you implement persistent connections to all BE JET databases, for your Access applications.<\/p><\/blockquote>\n
So how do you maintain a persistent connection?\u00a0 In your Utilities Module add…<\/p>\n
Public rsAlwaysOpen As Recordset<\/pre>\nIf you don’t have a Table that is open ALL the time create one.\u00a0 It doesn’t need any records it just needs to be a Tables linked in the Front end.\u00a0 Then on a your Main Form or a hidden Form in the On_Open event procedures add…<\/p>\n
Set rsAlwaysOpen = CurrentDb.OpenRecordset(\"YourTableNameHere\")<\/code><\/p>\nNow, you want to make sure you close that connection when exiting your database.\u00a0 So, on the On_Close event of the Main Form add…<\/p>\n
rsAlwaysOpen.Close\r\nSet rsAlwaysOpen = Nothing<\/pre>\nNow you will have a persistent connection which will improves your use of the database not having to reopen it every time you open a Form.<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> <\/div>\n","protected":false},"excerpt":{"rendered":"
You just went grocery shopping and your car is full of groceries. Which is faster?<\/p>\n
Take bag from car, open door to house, put bag inside house, go back out door closing and locking it behind you, repeat as many times till done<\/p>\n
OR<\/p>\n
And, here’s the persistent connection part… Take bag from car, open […]<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> <\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[49],"tags":[53],"class_list":["post-66","post","type-post","status-publish","format-standard","hentry","category-database-design","tag-persistent-connection","odd"],"_links":{"self":[{"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/66","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=66"}],"version-history":[{"count":9,"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/66\/revisions"}],"predecessor-version":[{"id":486,"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/66\/revisions\/486"}],"wp:attachment":[{"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=66"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=66"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=66"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}