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
malanoisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.