By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,292 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

lock table

P: 3
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
Feb 12 '08 #1
Share this Question
Share on Google+
6 Replies


puppydogbuddy
Expert 100+
P: 1,923
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.
Feb 13 '08 #2

P: 3
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
Feb 13 '08 #3

puppydogbuddy
Expert 100+
P: 1,923
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..
Feb 13 '08 #4

P: 3
nope did not change anything,

do not know what to do, or try

Daniel
Feb 14 '08 #5

puppydogbuddy
Expert 100+
P: 1,923
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

Expand|Select|Wrap|Line Numbers
  1. Function CompactDb(strSourceDB As String, strDestDB As String)
  2.  
  3. Dim jetEngine As JRO.JetEngine
  4. Dim strSourceConnect As String
  5. Dim strDestConnect As String
  6.  
  7.  
  8. ' Build connection strings for SourceConnection
  9. ' and DestConnection arguments
  10. strSourceConnect = "Data Source=" & strSourceDB
  11. strDestConnect = "Data Source=" & strDestDB
  12.  
  13. Set jetEngine = New JRO.JetEngine
  14.  
  15. jetEngine.CompactDatabase strSourceConnect, strDestConnect
  16.  
  17. Set jetEngine = Nothing
  18.  
  19. End Function
Feb 14 '08 #6

puppydogbuddy
Expert 100+
P: 1,923
Daniel,

If the temporary table is part of the tables collection, the following code should work: See this link
Expand|Select|Wrap|Line Numbers
  1. DoCmd.DeleteObject acTable, "tablename"
  2. 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.

Expand|Select|Wrap|Line Numbers
  1. Dim myTable As String
  2. myTable = "NameOfTable"
  3. If CurrentDb.TableDefs(myTable).Name > "" Then
  4. CurrentDb.Execute "DROP TABLE [" & myTable & "]"
  5. Else
  6. Exit Sub
  7. 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.
Feb 14 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.