malanois
asked on
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
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
ASKER
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.Connec
"Data Source=" & DBNAME & ";Persist Security Info=False"
Me.OleDbDataAdapter1.Fill(
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.Connec
"Data Source=" & DBNAME & ";Persist Security Info=False"
Me.OleDbDataAdapter1.Fill(
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.Connec
"Data Source=" & DBNAME & ";Persist Security Info=False"
Me.OleDbDataAdapter1.Fill(
oledbconnection1.close
Me.OleDbConnection1.Connec
"Data Source=" & DBNAME & ";Persist Security Info=False"
Me.OleDbDataAdapter1.Fill(
oledbconnection1.close
ASKER
That was already done.
and I'm still having the problem
and I'm still having the problem
ASKER
By the way these are select statements only
Alright, then.... is it acceptable to trap the error, and have the 2nd user wait until the database is available?
ASKER
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
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
>>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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Also, ensure your OLEDBConnection is closed as soon as you are done retrieving the data.