Access Err "Couldn't open ..File Path..File already in use? 1

[ClulessChris]

I have a number of shared access db's I administer. from time to time these db's 'lock' with no .ldb locking file and displays the err "Couldn't open "x:\Shared DB\Shared.mdb" file already in use"
This 'locks' the db to all users. could any body shed some light on why this happens or any work arounds / fixes?

 

[CCLINT]

Are you sure that you are opening them as Shared?
Are you use DAO or ADO?
Are you using the JET driver, or the ACCESS driver?

 

[ClulessChris]

CClint
Thanks for your reply, I use a connection string such as:
Dim DbDatabase as database
Dim rsRecordset as recordset

set dbDatabase = OpenDatabase(Filepath)
Set rsRecordset = dbDatabase.Openrecordset(SSql)

I started thread222-727992 to try and learn more about this and other sorts of connections in case this may be the route of these errors.

Everybody is Somebodys nutter

 

[ClulessChris]

CCLINT

I open with the connection string:

Dim db1 as Database
Dim rs1 as Recordset

Set db1 = OpenDatabase(filePath)
Set rs1 = db1.OpenRecordset(Sql)

I've tried to research other methods of connecction string in thread222-727992.

Everybody is Somebodys nutter

 

[CCLINT]

You are going to have to identify at what times in what situations the db is locking.
This may be happening when you are doing some certain db operation such as compacting or reparing in code, changing the table and field properties in code, opening the recordset for pessimistic locking and going into the Edit mode (the page will be locked until you call the Update method - this will also happen under optimistic locking, but only for the moment when the record's is actually being written to the db during the Update method).

Setting
Set db1 = OpenDatabase(filePath)
opens the db for shared mode as in:

Set db1 = OpenDatabase(filePath, False, False)

as this (open as shared) is the default.

Also:
Set rs1 = db1.OpenRecordset(Sql, dbOpenDynaset, ,dbOptimistic)


You may also want to check if the server is placing locks on the MDB - after all, an MDB is a flat-file db...

 

[ClulessChris]

CClINT sorry for the duplicate posts I'm haveing issues with this site at the moment.

"You are going to have to identify at what times in what situations the db is locking."
This is the hard part, the system is accessed by aprox' 150 users. The first I hear of the db loking is a phone call after the event. the application using these db's will one of a few senarios:

Set rs1 = DB.OpenRecordset(SQL,dbOpenSnapShot)

With rs1
Show the data
End with ' and set objects to Nothing


Set rs1 = bd.OpenRecordSet(Sql, dbOpenDynaset)
With rs1
.Edit
'Change some data
.Update
End with ' and set objects to Nothing

OR

Set rs1 = bd.OpenRecordSet(Sql, dbOpenDynaset)
With rs1
.Add
' add some new data
.Update
End with ' and set objects to Nothing

Is there anything in these methods that may be causing the issue?
I know the muli user is not ideal, but it's what I have to deal with.




Everybody body is somebodys Nutter.

 

[CCLINT]

Two things:

1. Post the

Set bd = xxx.OpenDatabase(xxx)

code

2. I cannot tell from the code you have posted if rs1 is being used to open 3 different recordsets with-out closing rs1 prior to setting it to nothing.

 

[ClulessChris]

CCLINT,
Sorry for the delay in updating this thread.

1. Set db = OpenDatabase(sFilePath,False, False, "PW;=Pword&quot

2. Each recordset is closed following use and a new recordset opened as required.

Could this err be caused in the event of two user trying to access the same record at exactly the same time?
If so this may be somthing I'll just have to "live with".

Everybody body is somebodys Nutter.

 

[johnvai]

Dear Friend:

This can't generate an error if you are using the default open mode as: set rs=db.openrecordset(Path)
This error occurs as long as you are in pessimestic mode.
The optimistic mode lets the first one editing go for it and the second one editing as well.
But you will be facing some errors in data.

You cannot let two users or more edit the same data at a specific period.
You can maybe trace the error and then retry for the update.