Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Persistent connection to MS Access using VB.Net

Posted on 2015-02-17
7
Medium Priority
?
255 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 59
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 85
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
Independent Software Vendors: 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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 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 59

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 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

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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 …

580 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