Microsoft Access
Microsoft Access

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:

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: 2017-11-15

Who is Participating?

LVL 54

Gustav BrockCIOCommented: 2017-11-16

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

LVL 86

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented: 2017-11-15

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

LVL 42

PatHartmanCommented: 2017-11-15

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.