'The database engine could not lock table' in Access 2016

I have a function that allows users to efectivly TRUNCATE an Access table.

It's a fairly basic function; it deletes all rows, and then resets the auto-increment index by changing the data-type of the ID column to INT, and then back to AUTOINCREMENT.

However, I'm running in to a problem; on occasion, when a user presses a button that calls the function, I'm halted by an error -

The database engine could not lock table 'Active Directory' because it is already in use by another person or process.

This error does not occur every time, but once it appears, I have to close Access to make it go away.

The error occurs on this line -

DB.Execute "ALTER TABLE [" & tableName & "] ALTER COLUMN [ID] INT" 

There table in question is abslolutly not open with Access, and even closing the table before calling this function does not work -

DoCmd.Close acTable, tableName, acSaveYes 

Is there anything I can do to avoid this error and get my code working as I require?

Function Truncate(ByVal tableName As String, Optional ByVal resetIndex As Boolean = True) As Boolean On Error GoTo checkResult ' Set up error handling Call Functions.setWarnings(False) ' Disable warnings Dim DB As DAO.Database Set DB = CurrentDb ' Set the DB object to use '** ' Clear the table contents '* DB.Execute "DELETE * FROM [" & tableName & "]" '** ' Change the data type of the ID field to INT '* DB.Execute "ALTER TABLE [" & tableName & "] ALTER COLUMN [ID] INT" '** ' Change the data type of the ID field back to AUTOINCREMENT DB.Execute "ALTER TABLE [" & tableName & "] ALTER COLUMN [ID] AUTOINCREMENT" '** ' Check the result of the TRUNCATE operation '* checkResult: Call Functions.setWarnings(True) ' Enable warnings Truncate = IIf(Err.Number = 0, True, False) ' Set the result End Function 

Related questions 4 What is the MS Access SQL syntax to create a field of type Hyperlink? 4 Access Alter Table "Allow Zero Length" 0 C#-Alter table tname add column cname Long Integer NOT NULL-throws exception Related questions 4 What is the MS Access SQL syntax to create a field of type Hyperlink? 4 Access Alter Table "Allow Zero Length" 0 C#-Alter table tname add column cname Long Integer NOT NULL-throws exception 0 Importing Excel SpreadSheets Into Access: Unable to Append All Data to the Table 1 Connect to Access Vba object model from Excel 1 Set username as default value in Access table 2 Alter Table doesn't work under MS Access 64 bit. Why? 0 Looping Through Multiple Access 2016 Tables to Create a Concatenated String Based on a Common Key In Each Table Load 5 more related questions Show fewer related questions

Know someone who can answer? Share a link to this question via email, Twitter, or Facebook.