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?