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:

Capture3.PNG
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.
Aravind RanganathanWindows Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'd suggest setting a Return Value, and then cleaning up BEFORE you return that value:

Function DoesTblExist(sTableName As String) As Boolean
  Dim RetVal 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)
 
   RetVal = True
 
Error_Handler_Exit:
   On Error Resume Next
   Set tdf = Nothing
   Set db = Nothing
   DoesTableExist = RetVal
   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
0
PatHartmanCommented:
1. Your queries are not valid Access SQL syntax
2. The Load event is too late to delete the table since it is already bound.

You might be able to do this in the Open event of the form but it would probably be better to do it in the form that opens this one so that the open is clean.

Whether you leave the tabledef and just delete rows or delete the table and make a new one, this practice will cause bloat so you probably don't want to do it directly into either the FE or BE.  

One of my clients uses a third party to manage all their data and the client's people get data from a data warehouse that is updated nightly for their analysis.  Some of the tables contain over 10 million rows and the Access app is really a stepping stone to get something that they can reduce and summarize enough to work comfortably with in Excel - although, I have converted some of the Analysts to doing all their work in Access.  When the Access apps do this, I use a "template" BE database.  It is saved with table definitions, PKs, and relationships.  The tables are empty, and the file has been compacted so it is as small as it can be.  The process I built copies a template and renames it and then runs append queries to add the current data into the empty BE.  Permanent data stays in the normal BE and only temporary data is kept in the template shell BE.  That way, whenever they need to refresh their data, they press a button and I copy the template and rename it which overlays the last copy and then import the data.  The user presses one button so he isn't really aware of what is going on behind the scenes.
0
Gustav BrockCIOCommented:
Don't delete the table, but delete the content:

Sub Copy()

    If DoesTblExist("LoadTableFinal") Then
        CurrentDb.Execute "Delete From LoadTableFinal", dbFailOnError
        CurrentDb.Execute "Insert Into LoadTableFinal Select * From [dbo_Load Table];"
    Else
        CurrentDb.Execute "SELECT [dbo_Load Table].* INTO LoadTableFinal FROM [dbo_Load Table];"
    End If
    Me.Requery

End Sub

Open in new window

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aravind RanganathanWindows Application DeveloperAuthor Commented:
Thank you all for the wonderful responses i am learning a lot about Access from you guru's
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.