Object open or anotehr user has the table open

[taytay]

Hello.

After using a form to update table "abc", I encountered the message below when opening the table "abc".

"Either an object bound to table 'abc' is open or another user has the table open. Do you want to open the table as read-only?"

Would appreciate if someone can help in the following

1) What is the usual cause of the above message?
2) How to resolve it so that I can open the table.

Thank you.

 

[ajetrumpet]

Hello.

After using a form to update table "abc", I encountered the message below when opening the table "abc".

"Either an object bound to table 'abc' is open or another user has the table open. Do you want to open the table as read-only?"

Would appreciate if someone can help in the following

1) What is the usual cause of the above message?
2) How to resolve it so that I can open the table.

Thank you.

the message is selft explanatory. do you indeed have an object open that is sourced by the table?

 

[taytay]

I am not aware of any open object. I used a DoCmd.Close acForm to close the form too. Will try to trace again.

Is there anyway to trace the opened object?

If there is indeed an open object source by the table, what is to be done? To close the table or form or recordset or what? If so, would appreciate if you can share the command. By the way, I use DAO.recordset to Update.

I read about acDialog but I am not certain how it work yet. Can it help in this instance?

Thank you. Sorry to pose so many questions. I am quite new to VBA...still trying to grasp.

 

[Scooterbug]

By the way, I use DAO.recordset to Update.

are you closing the recordset after the update?

ie: rs.Close

You might want to post the code that you are using...it might help troubleshoot the problem.

 

[ajetrumpet]

I am not aware of any open object. I used a DoCmd.Close acForm to close the form too. Will try to trace again.

Is there anyway to trace the opened object?

If there is indeed an open object source by the table, what is to be done? To close the table or form or recordset or what? If so, would appreciate if you can share the command. By the way, I use DAO.recordset to Update.

I read about acDialog but I am not certain how it work yet. Can it help in this instance?

Thank you. Sorry to pose so many questions. I am quite new to VBA...still trying to grasp.

i have no idea what is open and what is not, but you can check it with this code if you want:

Function GetOpenObjects()

Dim UserObjects(6) As Double
Dim ObjTypes(6) As String
Dim ObjName As String
Dim i As Integer
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("MyObjects", dbOpenDynaset)

UserObjects(0) = -32768
UserObjects(1) = 5
UserObjects(2) = -32764
UserObjects(3) = -32766
UserObjects(4) = 1
UserObjects(5) = -32761
UserObjects(6) = 3

ObjTypes(0) = 2
ObjTypes(1) = 1
ObjTypes(2) = 3
ObjTypes(3) = 4
ObjTypes(4) = 0
ObjTypes(5) = 5
ObjTypes(6) = 6

rs.MoveFirst

With rs
   Do Until .EOF
      If !Type <> -32757 And !Type <> 3 Then
         ObjName = !Name
            For i = 0 To 6
               If !Type = UserObjects(i) Then
                  If SysCmd(acSysCmdGetObjectState, ObjTypes(i), ObjName) = 1 Then
                     Debug.Print "Object " & ObjName & " is currently OPEN..."
                  Else
                     Debug.Print "Object " & ObjName & " is currently CLOSED..."
                  End If
                     Exit For
               End If
            Next i
      End If
               .MoveNext
   Loop
End With

rs.Close

Set rs = Nothing
            
End Function

the function opens a query a made manually. i can't seem to figure out how to loop the sysobjects table without getting a permissions error, so i made a query from it first. make this query and name it MyObjects before you run the above code:

SELECT MSysObjects.*
FROM MSysObjects
   WHERE (((MSysObjects.[name]) Not Like "MSYS" & "*" 
      And (MSysObjects.[name]) Not Like "~tmp" & "*"));

maybe call that function at the point in the code where you're running into this error. it'll tell you what's open and what's not