Solved

Persistent connection to MS Access using VB.Net

Posted on 2015-02-17
7
212 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

829 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