"You can't modify the structure of table "xyz", because it is already in use by another person or process.", and the Access instance cannot be closed

Question

While I changed some VBA code in MS Access to fill new columns that I added to an Access table, I struggled with a crashed instance that stayed open even if I clicked on "Close"/X.

I followed the first search hit [Access] You can't modify the structure of table because it is already in use by another person or process:

Try to reboot if you haven't already. If that doesn't work, try copying the database, then try to edit the copy. If it works, delete the original and rename the copy to what it was.

I wanted to avoid a full restart so that I made a copy of the file instead and worked in that. The good thing was that I was in a new instance in a new file, and I could also close it again. The instance was working. But when I added a new column in the table and tried to fill it, I ran into the error:

You can't modify the structure of table "xyz", because it is already in use by another person or process.

And what is more, the code did something, but it did not fill the table at hand, it worked on another hidden table, it seemed. I thought that would come from the crash so that the crashed instance was dominating the one that I was working in.

The code:

Option Compare Database

Sub DurchsucheAccessDatenbanken()
    Dim fso As Object
    Dim fld As Object
    Dim db As Object
    Dim rs As Object
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder("K:\MS Access\my_folder")
    
    Dim targetDB As Object
    Dim Objekttyp As String
    Dim Objektart As String
    Set targetDB = Application.DBEngine.Workspaces(0).OpenDatabase("K:\MS Access\my_file.accdb")
    
    For Each file In fld.Files
        file_Name = file.Name
        If Right(file_Name, 4) = ".mdb" Or Right(file_Name, 6) = ".accdb" Then
            Set db = Application.DBEngine.Workspaces(0).OpenDatabase(file.Path)
            
            ' Hier kannst du den Code ergänzen, um die Objekten der Datenbank auszulesen
            For Each obj In db.TableDefs
                obj_Name = obj.Name
                If Left(obj_Name, 4) <> "MSys" And Left(obj_Name, 1) <> "~" Then
                    Set rs = targetDB.OpenRecordset("my_table")
                    rs.AddNew
                    rs("Database").Value = file_Name
                    rs("obj_Name").Value = obj_Name
                    rs("LastUpdated").Value = obj.LastUpdated
                    rs.Update
                End If
            Next obj
    Next file
    
    targetDB.Close
End Sub

What should be done if you run into this?

Answer 1

The problem was that I had opened a new file "my_file2.accdb" during the crash and forgot to change the target database "my_file.accdb" in the code to "my_file2.accdb":

Set targetDB = Application.DBEngine.Workspaces(0).OpenDatabase("K:\MS Access\my_file2.accdb")

In the meantime, I had filled the "my_table" table in "my_file.accdb" with new rows instead. And when I then reopened the "my_file.accdb", I could work on that database again, and I could also close the instance again.