Lock/Unlock Table

[spinkung]

Hi All,

I am having problems filtering a list box contents.

I have 5 combo boxes to filter down the list box contents. There are some make table queries and delete table actions going on when the filter is applied.

Problem is is that when i filter the list and there are no results to show i sometimes get this message: runtime error 3211. the database could not lock the table 'tablename' because it is already in use by another person or process.

here's what i've got...

'PT1: delete existing table and create new tables based on crosstab
    DoCmd.SetWarnings False
    If TableExists("Table1") Then
        Me.lst_Skills_Lookup.RowSource = ""
        CurrentDb.TableDefs.Delete ("Table1")
    End If
'run the make table query
    DoCmd.OpenQuery "qry_Make_Crosstab_Table"
    DoCmd.SetWarnings True
    
'PT2: delete existing table and create new tables based on crosstab
    DoCmd.SetWarnings False
    If TableExists("Table2") Then
        Me.lst_Skills_Lookup.RowSource = ""
[COLOR="Red"]        CurrentDb.TableDefs.Delete ("Table2")[/COLOR]
    End If
    DoCmd.OpenQuery "qry_Make_Crosstab_Table2"
    DoCmd.SetWarnings True

The highlighted area is where the error kicks in. I am deleting then creating a table from 1 crosstab. Then i create and delete a second table from a crosstab based on the first table (confused?)

Is there a way i can unlock the table as it clearly isn't in use by anyone/anything?

Many Thanks.

 

[DCrake]

Off the top of my head I think is has got to do with your tabledefs. In your MSysObjects table access has marked the tabled for deletion form the schema next time a compact and repair is done. This is to enable an undelete action if done by mistake. And as such giving the make table the same name as the previous table it thinks it aready exists.

Have you closed your TableDefs correctly in your TableExists() function.

Set Tdfs = Nothing

 

[spinkung]

Thanks for your reply.

I understand what your saying and clearly it makes sense for access to do that incase you delete by accident.

I'm new to the whole tabledef thing so can you adivise me where i'm setting it to nothing??? Do i need to create a variable to hold my table def in??

'PT2: delete existing table and create new tables based on crosstab
    DoCmd.SetWarnings False
    If TableExists("tbl_LOOKUP_Skills_Matrix_FINAL") Then
        Me.lst_Skills_Lookup.RowSource = ""
        CurrentDb.TableDefs.Delete ("tbl_LOOKUP_Skills_Matrix_FINAL")
[COLOR="Red"]**Does it go here[/COLOR]
[COLOR="Red"]        Set CurrentDb.TableDefs = Nothing  *this doesn't work[/COLOR]
    End If
'run the make table query
    DoCmd.OpenQuery "qry_Tasks_Staff_Scores_Crosstab_FINAL_Make_Table"
    DoCmd.SetWarnings True

Thanks.

 

[DCrake]

If TableExists("tbl_LOOKUP_Skills_Matrix_FINAL") Then

This function needs to be examined to ensure that all tabledef references are destroyed. What does this code look like.

Here is a functio I use to determine what I am looking for is either a table or a query

Function IsTableQuery(DbName As String, TName As String) As Integer

   Dim db As DAO.Database, Found As Integer, test As String
   Const NAME_NOT_IN_COLLECTION = 3265

   ' Assume the table or query does not exist.
   Found = False

   ' Trap for any errors.
   On Error Resume Next

   ' If the database name is empty...
   If Trim$(DbName) = "" Then
      ' ...then set Db to the current Db.
      Set db = CurrentDb()
   Else
      ' Otherwise, set Db to the specified open database.
      Set db = DBEngine.Workspaces(0).OpenDatabase(DbName)

      ' See if an error occurred.
      If Err Then
         MsgBox "Could not find database to open: " & DbName
         IsTableQuery = False
         Exit Function
      End If
   End If

   ' See if the name is in the Tables collection.
   test = db.TableDefs(TName).Name
   If Err <> NAME_NOT_IN_COLLECTION Then Found = True

   ' Reset the error variable.
   Err = 0

   ' See if the name is in the Queries collection.
   test = db.QueryDefs(TName$).Name
   If Err <> NAME_NOT_IN_COLLECTION Then Found = True

   db.Close

   IsTableQuery = Found

End Function

 

[spinkung]

Thanks crake.

so i've created a mod with your function but how/where do i call it in my example, how do i examine my existing function (tableExists)?? does it clear my tabledefs?? apologies if i'm not seeing the obvious.

Ta.

 

[DCrake]

If you highlight the function TableExists then right-click on the mouse and select Definition from the menu (near bottom) it will take you to the function.

 

[spinkung]

Ok,

here's my check if table exists function...

Function TableExists(TableName As String) As Boolean

Dim strTableNameCheck
On Error GoTo ErrorCode

[COLOR="Green"]'try to assign tablename value[/COLOR]
  strTableNameCheck = CurrentDb.TableDefs(TableName)

[COLOR="Green"]'If no error and we get to this line, true[/COLOR]
  TableExists = True

ExitCode:
    On Error Resume Next
    Exit Function

ErrorCode:
    Select Case Err.Number
        Case 3265  [COLOR="Green"]'Item not found in this collection[/COLOR]
            TableExists = False
            Resume ExitCode
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "hlfUtils.TableExists"
            Resume ExitCode
    End Select
    
[COLOR="Green"]'Is this where i call your funtion???[/COLOR]
Call IsTableQuery(CurrentDb, strTableNameCheck)

End Function

 

[DCrake]

You function seems to be ok. The difference with mine is that it checks if it found a table or a query. You can remove it from your function.

 

[spinkung]

Hey Crake,

Thanks for your help on this. Still doing the same thing unfortunately. Every time the filter creates an empty table i get the error.

Sigh* i'll come back to it when my patience has repaired itself.

 

[spinkung]

I tried the SQL route and still get the same error. If i run the code and there is an empty table created as a result then the first time it runs i get a blank listbox, as expected. but if i run it a second time i get the error message 3211 again.

it says that it is locked or in use. is there a function i can use to test whether it's in use and unlock it if it is??

Thanks.

this is how the SQL version looks....

'PT2: delete existing table and create new tables based on crosstab
    DoCmd.SetWarnings False
    If TableExists("tbl_LOOKUP_FINAL") Then
        Me.lst_Skills_Lookup.RowSource = ""
[COLOR="Red"]        CurrentDb.Execute "drop table tbl_LOOKUP_FINAL", dbFailOnError[/COLOR]
    End If
'run the make table query
    DoCmd.OpenQuery "qry_Crosstab_FINAL_Make_Table"
    DoCmd.SetWarnings True

 

[RUdebugged]

Hi all,
I've got the same problem deleting a temporary table. Could the problem have to do with it being in an if/then block? I create it with a SQL SELECT statement, delete it with a SQL DROP statement but the error says the table is 'in use'...my code snippet:

dbs.Execute "SELECT tbltable1.* INTO [tblProctables] FROM tbltable1 " & _
"where [FilterID] = " & CStr(PanFilterIDs(I))
dbs.TableDefs.Refresh
If dbs.TableDefs("tblProctables").RecordCount = 0 Then
DoCmd.Close acTable, "tblProctables"
dbs.Execute "DROP TABLE [tblProctables];"
dbs.Execute "SELECT tbltable2.* INTO [tblProctables] FROM tbltable2 " & _
"where [FilterID] = " & CStr(PanFilterIDs(I))
dbs.TableDefs.Refresh
End If

In case you are wondering why...I need to make a recordset that is updateable (ie. not based on a union query) .

 

[GaryRamu@hotmail.com]

Experienced identical error message using a lookup dropdown on form where attempted in event procedure to delete and unbound table associated with this lookup...all I did to fix was add immediately before deleting table---me.refresh

If the combo is used during a session with form before a refresh, that would create the lock condition binding control with table. A form refresh cleared this. Again, table is not bound yet still used as a lookup in this case, not all like this, may lend clues.

Without adding me.refresh, would receive that same locking error message.

What I did to solve is work application in manner where error did not occur---identified action as a form refresh, added that to code, solved it.

If bound with relationships or other object binding, will need to execute code to roll back to an unbound state whether a refresh or other then roll forward---a form refresh will not work in all cases.

 

[MBMSOFT]

Experienced identical error message using a lookup dropdown on form where attempted in event procedure to delete and unbound table associated with this lookup...all I did to fix was add immediately before deleting table---me.refresh

If the combo is used during a session with form before a refresh, that would create the lock condition binding control with table. A form refresh cleared this. Again, table is not bound yet still used as a lookup in this case, not all like this, may lend clues.

Without adding me.refresh, would receive that same locking error message.

What I did to solve is work application in manner where error did not occur---identified action as a form refresh, added that to code, solved it.

If bound with relationships or other object binding, will need to execute code to roll back to an unbound state whether a refresh or other then roll forward---a form refresh will not work in all cases.

this really works.....
i was wondering what is that keep tables locked... and actually it is combo box in my case
as you wrote just refresh the form and tables connected to combo box are setting free and able to be deleted
thanks....

 

[Stanski21]

I got really excited then... I read all the way through this post nodding my head thinking 'this is going to solve my week long problem!'
And then... it didn't.

Something in my code is locking my Temp table... but only one of them! I have a Report and Sub Report run off data in two temp staging tables. I load up the DB, run a report and it works swimmingly. Back out of the report, change some of the parameters and... Nope and Nope.

I log out of the database (the temp tables still exist) and the code runs fine again. So something in the code is opening and not closing I guess and that is locking the table.

Here's the code:

Private Sub btnKPIReport1_Click()

Me.Refresh
Forms!frm_ReportType.Refresh
If Me!cboReportTeamNo = "" Then
MsgBox "Please Select a Team Number and try again", vbExclamation, "Oops!"
Me!cboReportTeamNo.SetFocus
Exit Sub
Else: GoTo RPTRUN
End If

RPTRUN:

Dim strsql As String
Dim strsql1 As String
Dim db As Database
Dim rs As DAO.Recordset
Dim strTbl As String

Set db = CurrentDb
strTbl = "tblKPI1Agent"
strsql = "SELECT * INTO " & strTbl & " FROM qry_FINALKPI1;"

DoCmd.SetWarnings False
DoCmd.Hourglass True

On Error GoTo ErrHandler
db.Execute "Drop Table " & strTbl & ";", dbFailOnError
DoCmd.OpenQuery "qry_FINALKPI1"
DoCmd.RunSQL strsql
DoCmd.Close acQuery, "qry_FINALKPI1", acSaveNo

DoCmd.SetWarnings True

KPITL1 'this does pretty much the same as this sub but uses SUM functions to give totals as a sub report

DoCmd.Hourglass False

DoCmd.OpenReport "rpt_FinalKPI1", acViewReport

ErrHandler:
Debug.Print Err.Number
If Err.Number = 3211 Then GoTo WTF
If Err.Number = 7874 Or Err.Number = 3326 Then Resume Next
If Err.Number <> 7874 And Err.Number <> 3326 Then Exit Sub

WTF:
DoCmd.Hourglass = False
If MsgBox("An error has occured. Please restart the DB", , "SMH!") = vbOK Then Exit Sub

End Sub

 

[static]

If you've based a report on the table, the fields are fixed, it's not temprorary at all and I don't get why you'd want to delete it. Just empty it.

Private Sub btnKPIReport1_Click()

	const strTbl As String = "tblKPI1Agent"
	
	currentdb.Execute "delete * from " & strTbl
	currentdb.Execute "INSERT INTO " & strTbl & " SELECT * FROM qry_FINALKPI1;"

	DoCmd.OpenReport "rpt_FinalKPI1", acViewReport

End Sub

Of course unless rpt_FinalKPI1 is super slow and used a lot you shouldn't even be doing that because it will bloat the database.