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:
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.
Private Sub Form_Load()
Call Copy
End Sub
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
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
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.
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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.
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.
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.
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
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 trialIt'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.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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