Solved

Persistent connection to MS Access using VB.Net

Posted on 2015-02-17
7
203 Views
Last Modified: 2016-02-11
I've read that a persistent connection from each client application to an Access file can improve performance in a multi-user environment. The connection is opened when the app is started and closed when the app is closed. Can such a connection be established from a vb.net application? If so, how?
0
Comment
Question by:Tim313
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 75
ID: 40615877
Yes ... it makes a HUGE difference.

Do you have access to DAO via vb.net ?

In VBA, all you actually have to do is execute an OpenDatabase command. That will create the LDB (lACCDB) file.

mx
0
 
LVL 57
ID: 40616159
I would add that it depends on the app.   If you are already more or less holding a connection open with the way the app works (say with a menu utility) , then no, you won't see any difference.

 What happens and where the recommendation comes from is that once a client has no more active connections, the database is closed for that user.   When they go to do something again, the database is opened again.

 The overhead of getting a user logged into JET and the database opened is fairly expensive overall.

 So by holding a persistent connection (be it opening the DB itself or even a table), you avoid the repeated closing/opening.

 In vb.net, you'd create a class to open a table, returning a record set (ADO or DAO, doesn't matter) from a table with no records (SELECT * FROM <table> WHERE 1=0), and then hold an instance of that class open until the app closes.

Jim.
0
 
LVL 84
ID: 40616187
Generally with .NET you should open the connection when needed, do the work, then close the connection immediately. ADO.NET uses connection pooling, and you should allow ADO.NET to manage your connections. .NET handles connections differently than does Access, so it'd be hard to say if this method would work with .NET. That said, as JimD mentions, I'm not sure how ADO.NET works in regard to file-based databases, like JET/ACE. You may find that opening a connection will improve performance.

Note that if you have a Main form that's always open you could do the same in the Load event of that form. Just do something like this:

Dim con As New OleDb.Connection
con.ConnectionString = "YourConnectionString"
con.Open
Dim cmd As New OleDb.Command
cmd.Connection = Con
cmd.CommandText = "SELECT IDField FROM YourTable WHERE 1=0"
Dim dt As New Datatable
dt.Load(cmd.ExecuteReader)

Don't use the "Using" construct, since doing so would destroy those objects after the code block ...
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Tim313
ID: 40616243
Thanks guys, all good information.

Once the persistent connection is opened, is this the connection I need to use for all subsequent queries in my app (currently each query opening/closing a connection)?
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 40616265
is this the connection I need to use for all subsequent queries
IMO, no. You should create/open a connection when needed, and close it when you're finished.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 40616333
<<is this the connection I need to use for all subsequent queries in my app (currently each query opening/closing a connection)? >>

 No, there is nothing special about this connection.  All it's doing is making sure the database doesn't close when you stop doing everything else.

Jim.
0
 

Author Closing Comment

by:Tim313
ID: 40616481
Thanks Scott and Jim!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question