Error Message in access says someone uses the database,and no one does

[Husameddin]

Whenever I want to update my database I can’t save any changes to it and I got this message "you don’t have exclusive access to the data base at this time".or you can't save. anther user has the file open and i must have exclusive access to the file
Anther icon appears in the desktop with a lock in it has the same name as the data base. I am using office xp and some times office 97 but I convert my data base whenever I use different version. I used some VBA codes in my database and I don’t know if that is the problem.
Please help me

 

[JoyInOK]

One possibility is that your VBA code may have left an invisible copy of the database running. Reboot your computer to close all programs. Check your code to see if you have something like the following:
dim dbMine as database
set dbMine = currentdb
'other code

If not already in your code, add the line
set dbMine = Nothing
to release the variable and the space it occupies in memory.
This may also happen if you open an instance of Word to do a mail merge that uses Access for the data. Be sure to set your variable that references the Word instance to Nothing as well.

Here's a module that will tell you who's logged into your database, even if it's multiple instances that you alone have started. The computer names will print in the Immediate window. Paste the following code in a new module.
Add a table to the database called tblDatabaseUsers with text fields UsersName and ComputerName (you can use other names and change them in the code below, but watch out for VB reserved words). You can add the computer and user data to the table now, or you can run the code without. Adding the user data is handy if your computernames are something like "12354-RX1," but its not worth the effort if they are something like "SmithR," since it's obvious who the user is.
Then, when you get the error message, switch to design view of any form. In the Immediate window, type "ShowCurrentUsers" and hit Enter.

Sub ShowCurrentUsers()
Dim cn As New ADODB.Connection
Dim cn2 As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long

cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open "Data Source=" & CurrentDb.Name
cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & CurrentDb.Name

' The user roster is exposed as a provider-specific schema rowset
' in the Jet 4 OLE DB provider. You have to use a GUID to
' reference the schema, as provider-specific schemas are not
' listed in ADO's type library for schema rowsets

Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
, "{947bb102-5d43-11d1-bdbf-00c04fb92675}&quot

'Output the list of all users in the current database.

Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, _
"", rs.Fields(2).Name, rs.Fields(3).Name
Dim strCompName As String
While Not rs.EOF
strCompName = Left((rs.Fields(0)), (Len(RTrim(rs.Fields(0))) - 1))

If IsNull(DLookup("[UsersName]", "tblDatabaseUsers", "[ComputerName]='" & strCompName & "'&quot) Then

Debug.Print rs.Fields(0)
Else
Debug.Print DLookup("[UsersName]", "tblDatabaseUsers", "[ComputerName]='" & strCompName & "'&quot
End If
rs.MoveNext

Wend

End Sub

 

[LittleSmudge]

Alternativly, a user craching out of Access could have left the mdl file still thinking they are logged in.

Use Windows explorer to delete the .mdl file.

IF it delete okay then the above ( or something similar ) was probobly true.
If it refuses to delete then you need to look further.


'ope-that'elps

G LS G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[Husameddin]

thank you guys for your help. I decided to copy the tables and the forms to anther new database, when I did this it worked without any problem and I could save my work ;o)

about the code

set dbMine = Nothing

could you tell me where to put please JoyInOK.

this is a great website

 

[JoyInOK]

Glad you got it worked out! The conversion wizard that changes your db from 97 to 2000 may be part of the problem on that one. I had to do the same solution after convert, but for a different symptom.

Release your variable just before exiting or ending the sub.
It can't hurt to add it to your error handler too:
Private Sub cmdCentral_Click()
On Error Goto ErrHandler
Dim dbMine as database
Set dbMine = currentdb
'other code that manipulates your database

set dbmine = Nothing
exit sub
ErrHandler:
Msgbox "Err #" & Err.Number & " occurred." & err.description, vbokonly, "Error"
set dbmine = Nothing
End Sub