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

How to unlock a table?

P: 17
I am using the code below,
In one of the sections i am running a query to delete all records in the db,
then a short while later i import the records intothe


DoCmd.OpenQuery "qryDelConfirmationTable", acViewNormal

DoCmd.TransferText acImportFixed, "alCouponSpecsImport2", "tblALConfirmFile", Filename

After that i attempt to build a new table wiht the current information, this returns a runtime error 3211, saying the database could not lock the table because it is in use.
I have read up on 3211 and the "solutions" did not help, i get the same thing. I tried closing the connection and redefining the connection same thing.
What am i doing wrong?



Expand|Select|Wrap|Line Numbers
  1. Private Sub CmdXLBrowse_Click()
  2.  
  3. Dim Filename As String
  4. Dim db As Database
  5. Set db = CurrentDb()
  6. ' do things with db, not currentdb().
  7. Set db = Nothing
  8.  
  9.     'Me.txtXLFIle = ""
  10.     Me.txtXLFIle.Value = FindFile(Me.txtXLFIle.Value, "Please Select a Text File", "Text Files", "*.tx?")
  11.     'Me.txtXLFIle.Value = FindFile(Me.txtXLFIle.Value, "Please Select an Excel File", "Excel Files", "*.xl?")
  12.  
  13.     Filename = Nz(Me.txtXLFIle.Value, "1")
  14.  
  15.     'Clean Up
  16.     'DoCmd.SetWarnings False
  17.     DoCmd.OpenQuery "qryDelConfirmationTable", acViewNormal
  18.     Debug.Print "Old Confirmations Erased"
  19.     DoCmd.OpenQuery "qryDelCouponItemsTable", acViewNormal
  20.     Debug.Print "Old Vouchers Deleted"
  21.  
  22.     DoCmd.TransferText acImportFixed, "alCouponSpecsImport2", "tblALConfirmFile", Filename
  23.     'Drop Table deletes from database
  24.     'CurrentDb.Execute "Drop table tblALConfirmFile"
  25.  
  26.     DoCmd.OpenQuery "qryALCreateCouponDataFile", acViewNormal
  27.     DoCmd.OpenForm "frmALCoupon 7-24-08", acNormal
  28.     'DoCmd.SetWarnings True
  29.  
  30. On Error GoTo MyErrorCheck
  31. MyErrorCheck:
  32.     If Err.Number = 3011 Then
  33.     ' Do something here
  34.     MsgBox "Please Try Again", vbOKOnly
  35.     Resume
  36.     End If
  37.  
  38.     If Err.Number = 7874 Then
  39.     ' Do something here
  40.     Resume
  41.     End If
  42.  
  43.     If Left(Trim(txtXLFIle), 1) = "'" Then
  44.     ' comment line, skip it
  45.     End If
  46.  
  47. End Sub
  48.  
Jul 25 '08 #1
Share this Question
Share on Google+
1 Reply


nico5038
Expert 2.5K+
P: 3,072
Two remarks:
1) Better to use:
currentdb.execute ("qryDeleteConfirmations")
this won't open the query and keep the table locked, but just delete the rows.
2) When there's a relation between Confirmation and CouponItems, be sure to remove the "children" before the "parent" when no Cascading delete has been defined as a relation.

Nic;o)
Jul 27 '08 #2

Post your reply

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