Solved

Persistent connection to MS Access using VB.Net

Posted on 2015-02-17
7
221 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying 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

Suggested Solutions

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…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 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