Solved

Persistent connection to MS Access using VB.Net

Posted on 2015-02-17
7
197 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now