Link to home
Create AccountLog in
Avatar of Aravind Ranganathan
Aravind Ranganathan

asked on

Table Getting locked when opening the applicaiton form

i have an access application on form load i have this particular code that checks if a certain table exists if it does then drop it and insert into it making it a new table, if it doesn't exist then just insert into the table. but when i run the form itself it says:

User generated image
Private Sub Form_Load()
Call Copy
End Sub

Open in new window


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

Open 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

Open in new window



is there a way i can load the application deleting all the locks via code if not what is causing the table to be locked without any activity performed on it.
SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Aravind Ranganathan
Aravind Ranganathan

ASKER

Thank you all for the wonderful responses i am learning a lot about Access from you guru's
You are welcome!

/gustav