• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 180
  • Last Modified:

MS Access File Open Error

Is there a way to to have multiple connections to an Access DB, from different users Simultaneously from a VB.Net application.

I have created a program with the Access DB located on a shared drive.  If user 1 starts a search on the db it works great.

If user 1 starts his search and 2 seconds later user 2 starts his search.  I get an error on user 2 machine.  File Open

how can I correct this, without going to SQL


MJ
0
malanois
Asked:
malanois
1 Solution
 
Jeff CertainCommented:
You need to ensure that the connection to Access is not opened exclusively. You also need to change the record-locking from page-locking(the default) to record-locking to reduce the number of lock conflicts.

Also, ensure your OLEDBConnection is closed as soon as you are done retrieving the data.
0
 
malanoisAuthor Commented:
You need to ensure that the connection to Access is not opened exclusively.

Is this in the connection string?


You also need to change the record-locking from page-locking(the default) to record-locking to reduce the number of lock conflicts.
This is somewhere in the DB Properties correct?


  Me.OleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & DBNAME & ";Persist Security Info=False"

            Me.OleDbDataAdapter1.Fill(Me.DbDataSet)
0
 
Jeff CertainCommented:
The record locking mode needs to be set from Access  -- Tools >> Options >> Advanced. Set the default open mode to shared, set the default locking to no locks, and check the box that says "use record-level locking".

Me.OleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & DBNAME & ";Persist Security Info=False"

            Me.OleDbDataAdapter1.Fill(Me.DbDataSet)
oledbconnection1.close
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
malanoisAuthor Commented:
That was already done.

and I'm still having the problem
0
 
malanoisAuthor Commented:
By the way these are select statements only
0
 
Jeff CertainCommented:
Alright, then.... is it acceptable to trap the error, and have the 2nd user wait until the database is available?
0
 
malanoisAuthor Commented:
Ill try it,

but I could have hundreds of people connecting at once.

i dont think that would be good to the user who is the 200th in line.

what would be a way of waiting until the Db is available?

Thanks,

MJ
0
 
flavoCommented:
>>but I could have hundreds of people connecting at once.

If this is the case, stop your quest and move to MS SQL.  Access IS NOT going to be fast at all with this many  simultanious connections, its going to be painfully SLOW!

This still doesnt resolve you issue.. I have several apps in VB .Net with many (up to 20) connections.  Speed drops alot with anything over 10 - 15 concurrent connections.

For connection strings see -> www.connectionstrings.com , this is the one i use and NEVER had the same problem.


Dave
0
 
planoczCommented:
What you need to do is to use datasets with all your users and have all your connections just
go grab your data and close connection. Then do your work dis-connected and when you are finish then update your database. You can use ADO.Net with Access.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now