lock table

[DanielBertin]

Hi all,
would someone know how to unlock a table in msaccess,vba?

a combobox on update gives me an error table lock

now I tried everything I can think of

'DoCmd.Close acTable, "tmpCatgoryProductTable"

' DoCmd.Close acQuery, "qryDistinctCat"
' DoCmd.DeleteObject acTable, "tmpCatgoryProductTable"

DoCmd.Close acQuery, "qrySelectProductList"

DoCmd.Close acQuery, "qryDistinctCat"
DoCmd.Close acTable, "tmpCatgoryProductTable"
DoCmd.Close acQuery, "qrySelectDivisionProducts"

MsgBox ("afterupdate")
DoCmd.OpenQuery "qrySelectDivisionProducts"

but the qry which reads from the tmpCatgoryProductTable, gives me that the table is lock

Ideas please
Daniel

[puppydogbuddy]

Hi all,
would someone know how to unlock a table in msaccess,vba?

a combobox on update gives me an error table lock

now I tried everything I can think of

'DoCmd.Close acTable, "tmpCatgoryProductTable"

' DoCmd.Close acQuery, "qryDistinctCat"
' DoCmd.DeleteObject acTable, "tmpCatgoryProductTable"

DoCmd.Close acQuery, "qrySelectProductList"

DoCmd.Close acQuery, "qryDistinctCat"
DoCmd.Close acTable, "tmpCatgoryProductTable"
DoCmd.Close acQuery, "qrySelectDivisionProducts"

MsgBox ("afterupdate")
DoCmd.OpenQuery "qrySelectDivisionProducts"

but the qry which reads from the tmpCatgoryProductTable, gives me that the table is lock

Ideas please
Daniel

This sounds like a problem with your Lock DB (ldb) file not closing as it should when you exited your application. Without booting your application, go to the directory where your mdb file is and see if there is file with the same name as your mdb, but with the extension ldb. If there is, you need to delete it. If it won't allow you to delete, you have to reboot windows and then delete the file. After deleting the file, the lock should be cleared. You need to fix the problem query or you will lock up the application again.

[DanielBertin]

a query creates a temptable and locks the table, I need to reuse the query and recreate the table, on a combobox change.

but it will not allow me, since it is locked. how can I clear the lock on the tmptable? I tried closing/opening the query, and closing the table. it is still locked

I tought maybe there would be a way to do this in code.

thanks
Daniel

[puppydogbuddy]

a query creates a temptable and locks the table, I need to reuse the query and recreate the table, on a combobox change.

but it will not allow me, since it is locked. how can I clear the lock on the tmptable? I tried closing/opening the query, and closing the table. it is still locked

I tought maybe there would be a way to do this in code.

thanks
Daniel

Try a DB compact and repair and see if that unlocks the table for you. Let me know if i works for you..

[DanielBertin]

nope did not change anything,

do not know what to do, or try

Daniel

[puppydogbuddy]

nope did not change anything,

do not know what to do, or try

Daniel

Try compacting with the Jet Compact utility. It corrects problems that Access Compact does not. Code obtained from tips page of www.aadconsulting.com. If this does not do the trick, let me know. I have at least 2 other things for you to try.

Compact a Database Using Jet

Function CompactDb(strSourceDB As String, strDestDB As String)
 
Dim jetEngine As JRO.JetEngine
Dim strSourceConnect As String
Dim strDestConnect As String
 
 
' Build connection strings for SourceConnection
' and DestConnection arguments
strSourceConnect = "Data Source=" & strSourceDB
strDestConnect = "Data Source=" & strDestDB
 
Set jetEngine = New JRO.JetEngine
 
jetEngine.CompactDatabase strSourceConnect, strDestConnect
 
Set jetEngine = Nothing
 
End Function

[puppydogbuddy]

Daniel,

If the temporary table is part of the tables collection, the following code should work: See this link

DoCmd.DeleteObject acTable, "tablename"
On Error Resume Next

where tablename identifies the table you need to delete before creating it with the Execute method. Now, let's suppose the table doesn't exist. In that case, the above statement will create an error, which is handled nicely with an On Error statement in the form
___________________________________________


If the above does not work, you can try this if the temp table is part of the Tables Collection.

Dim myTable As String
myTable = "NameOfTable"
If CurrentDb.TableDefs(myTable).Name > "" Then
CurrentDb.Execute "DROP TABLE [" & myTable & "]"
Else
Exit Sub
End If

__________________________________________________ _

if none of the above suggestions worked, then I would proceed as if my database were corrupted. The usual procedure followed is to create a new blank database, and then import all of the objects in the problem database, except for the temporary table that you can't delete or unlock.

Hope this helps.