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

What sort of error?

SELECT INTO cannot put records into an existing table, so if "LoadTable" already exists you'll get an error.

@Scott McDaniel the query above was working before it just stopped working, and if  i make it into an append query i am getting this error: the query cannot be completed. either the size of the query result is larger than the maximum size of  the database , or there is not enough temporary storage space on the disk to store the query result.

Then don't use access, but the sql
It hasn't got those limits

And don't listen to managers for technical stuff !

the query above was working before it just stopped working

I'm not sure how since DELETE * should return error. Anyway if you want to delete all rows from a table then try the TRUNCATE command:

TRUNCATE TABLE WorkTable

Select allOpen in new window

Access databases are limited to 2 GB in size, and that includes any "temporary" tables/databases created to manage your processes. SELECT INTO would certainly create quite a few of those temporary tables, which would increase the size of your Access database. If you're trying to move over a large amount of data, or if you already have a database that is near that limit, you might be running into that 2 GB limitation.

Poorly performing Access databases are often the result of many things - improperly built tables, poor indexing, etc etc. You might be better off figuring out why your Access db is not performing well rather than trying this sort of approach.

And if your SELECT INTO process worked before, that would mean the table did not exist. SELECT INTO creates a NEW TABLE when run. Obviously, if you try to create a new table with the same name as an existing table, the process will error.

Vitor: DELETE * works in Access, but of course not in SQL. When running linked tables in Access, the query engine allows you to mix/match these sorts of things.

@Scott McDaniel so what are my options here? Insert Into  is causing storage issues and cant copy over 200k records and its very slow, the Select Into is pretty fast but complains about not being able to create a lock on the table because the table was already created. what do you suggest bud??

Select Into is pretty fast but complains about not being able to create a lock on the table because the table was already created

Drop the table before you use SELECT INTO:

Currentdb.Execute "DROP TABLE YourTableName"
Currentdb.Execute "SELECT INTO blah blah", dbFailOnError

@Scott McDaniel that error is back to haunt me :(  i am not getting that error while dropping the table and the table is not open. i am thinking some process is locking the table in the back is that any code in access that could say maybe release all locks on the form load event?

Remove the square brackets and add dbFailOnError:

Currentdb.Execute "DROP TABLE LoadTable", dbFailOnError

Also remove the "DELETE" line. Doesn't make much sense to delete everything from a table you're going to DROP.

Be sure of spelling also. Your SELECT INTO refers to "Load Table" and "LoadTable". I realize they're in different databases, but it's easy to mix things up.

Also remove the "DELETE" line. Doesn't make much sense to delete everything from a table you're going to DROP.

They aren't the same table. In fact the table that he is deleting doesn't appear after in the code.

@Scott McDaniel still not working getting the same error could not lock table, this is the current code i am using:

Sub Copy()
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM WorkTable"
Me.Refresh
CurrentDb.Execute "DROP TABLE LoadTable", dbFailOnError
Me.Refresh
CurrentDb.Execute "SELECT [dbo_Load Table].* INTO LoadTable FROM [dbo_Load Table];"
DoCmd.SetWarnings True
End Sub

Select allOpen in new window

@Scott McDaniel i got it working so i check for table exist before dropping it and in the first run it doesn't find the table and works good then when i run the same code again this time it notices the table there and does not drop it and throws the could not lock load table because it is being used by another process. why is the application not releasing the lock?

Sub Copy()
DoCmd.SetWarnings False
If DoesTblExist("LoadTable") Then
CurrentDb.Execute "DROP TABLE LoadTable", dbFailOnError
Me.Refresh
CurrentDb.Execute "SELECT [dbo_Load Table].* INTO LoadTable FROM [dbo_Load Table];"
Else
CurrentDb.Execute "SELECT [dbo_Load Table].* INTO LoadTable FROM [dbo_Load Table];"
DoCmd.SetWarnings True
End If
End Sub 

Select allOpen in new window

Function DoesTblExist(sTableName As String) As Boolean
   Dim db   As DAO.Database
   Dim tdf  As TableDef
 
On Error GoTo Error_Handler
   'Initialize our variable
   DoesTblExist = False
 
   Set db = CurrentDb()
   Set tdf = db.TableDefs(sTableName)
 
   DoesTblExist = True  'If we made it to here without triggering an error
                        'the table exists
 
Error_Handler_Exit:
   On Error Resume Next
   Set tdf = Nothing
   Set db = Nothing
   Exit Function
 
Error_Handler:
   If Err.Number = 3265 Then
      'If we are here it is because the table could not be found
   Else
      MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
      Err.Number & vbCrLf & "Error Source: DoesTblExist" & vbCrLf & "Error Description: " & _
      Err.Description, vbCritical, "An Error has Occured!"
   End If
   Resume Error_Handler_Exit
End Function

Select allOpen in new window