asked on

"Could not use ''; file already in use." error message when accessing Access database file in VB or Access

In VB6 using an ADO connection, my application will sometimes generate the error "Could not use ''; file already in use." . Folder settings where DB resides is set to Full control - all users.
Even Access itself can not open the file when I get this error. I tried deleting the ldb file, but it would not let me. The only thing that works is re-booting the server (Win Server 2003) Then everyone can ope the db again. It almost looks like when a workstation runs the application for the first time, this happens. Then we reboot the server and all is good, but can't confirm this.

Set cnnRepairDB = New ADODB.Connection
Set recRepair = New ADODB.Recordset
Set recBlank = New ADODB.Recordset
 
cnnRepairDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & DRIVELTR & ":\Program Files\Pallet-Track\Data\PTRepairProgram.mdb;"
 
SearchSQL = "SELECT * FROM [ClosedRepairs] WHERE Customer = '?'"
               
            With recBlank
                .ActiveConnection = cnnRepairDB
                .CursorLocation = adUseServer
                .CursorType = adOpenKeyset
                .LockType = adLockOptimistic
                .Properties("IRowsetIdentity") = True
                .Open SearchSQL, , , , adCmdText
            End With
 
SearchSQL = "SELECT * FROM [ClosedRepairs] ORDER BY [SortDate] Desc;"
               
            With recRepair
                .ActiveConnection = cnnRepairDB
                .CursorLocation = adUseServer
                .CursorType = adOpenKeyset
                .LockType = adLockOptimistic
                .Properties("IRowsetIdentity") = True
                .Open SearchSQL, , , , adCmdText
            End With
 
Set DataGrid1.DataSource = recRepair

Open in new window

Scott McDaniel (EE MVE )

I would presume it's because your datagrid is holding the connection open ... you should probably explicitly release this when your program exits, and close your recordsets and connections (or set them to nothing). A hung lockfile usually indicates that something, somewhere, still has a connection.

almiceli

ASKER

The ADO datagrid's datasource is a recordset with optimistic recordlocking. It does not lock the records. ADO will lock only the single record that is being added or modified, then release it when its done. I can and have opened this type of grid over and over again, without any problems.
The issue I think is that somehow the databse is being opened exclusively, but nowhere am I opening it in Exclusive mode. I suspect Windows Server 2003 permissions, but the folder has been shared with full control over all users.

Scott McDaniel (EE MVE )

<ADO will lock only the single record that is being added or modified, then release it when its done>

While your ADO recordset may support this, Access/Jet does not. Jet is a file-based database, and the workstation performs all the necessary work which means that the workstation will open the entire table (unless you use ONLY indexed fields in your query or SQL). The Access environment __can_ support row-level locking, but it is very hit and miss and, in most cases, setting the db to row-level locking simply doesn't work.

You could possibly have a corrupt database. Try rebuilding your database by building a new database, and importing all objects into the new database.

The folder that hosts your database requires full permissions for all users, but it appears that you've got that. If the lockfile is being generated in that folder, then it would appear that your permissions are correct.

Jim P.

>> I tried deleting the ldb file, but it would not let me.

That tells me some machine has the the database open in Exclusive mode.

Next time it happens go into Computer Management (compmgmt.msc from the Run or Right Click My Computer and select Manage). Under Computer Management --> System Tools --> Shared Folders --> Open Files and see if anyone is connected to that file.

almiceli

ASKER

You are correct, another computer has it open in exclusive mode, but i don't know why. It appears that the first time they run the program, this happens.
Then we have to re-set the server (data resides there) and hey can run the program without locking the db files.
I think this has something to do with Server 2003 permissions but can not figure out what it is. Te file is set to full control for all users.
Its not my code. I use the same s/w all over and never a problem. And I do close the connections and set the recodsets to nothing.

Jim P.

Once you find the offending machine/user go to that PC. In Access go to Tools --> Options --> Advanced tab and see what is checked for Default open mode.

Make sure it is set to shared.

almiceli

ASKER

I checked the Access default settings on those machines that had Access. None were set to Open Exclusive. This happens even if the workstation does not have Access installed on it.

ASKER CERTIFIED SOLUTION

Jim P.

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim P.

Glad to be of assistance. May all your days get brighter and brighter.

&quot;Could not use ''; file already in use.&quot; error message when accessing Access database file in VB or Access

"Could not use ''; file already in use." error message when accessing Access database file in VB or Access