Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2007
    Posts
    48

    Unanswered: Access table will not lock

    I am trying to copy and delete tables, but when I go to delete I get the following error message:

    Runtime error 321
    The database engine could not lock table
    "tblLatestTemps" because it is already in use by
    another person or process
    This error occurs in the line shown below. Can anyone please suggest where I might be going wrong in the code ? All help appreciated.

    kind regards,
    Jill

    Code:
    Sub Fetch_Temp_Data(Excelpath As String)
    'After checking that the archive table tblTemps_Archive exists, and creating it if necessary,
    'this procedure will import an Excel spreadsheet Temp Report.xls
    'which contains the latest  temperature data,
    'and transfer this data into  database table tblLatestTemps
    
    
    
    Dim Db As DAO.Database, rst As DAO.Recordset
    Dim fld As DAO.Field
    Dim TableName1$, TableName2$
    
    Set Db = CurrentDb()
    'MsgBox "This procedure will import the Excel workbook [Latest Temp Report.xls]" 
    'Excelpath = myFilename  
    
    
    'check the number of columns in this Excel file to be imported  contains the same number of columns as the existing Db
    
    
    TableName1 = "tblLatestTemps"   ' T1
    TableName2 = "tblTemps_Archive" ' T2
    
    'test if 1st table T1 exists
    If TableExists(Db, TableName1) Then
        
        
        'test if 2nd table T2 exists
        If TableExists(Db, TableName2) Then
            
            'delete the table T2 before copying T1
            DoCmd.DeleteObject acTable, TableName2
            
            'copy T1 to T2
            DoCmd.CopyObject , TableName2, acTable, TableName1
             'delete Table T1  (OK as archive exists)
            DoCmd.DeleteObject acTable, TableName1   <<<< RT ERROR HERE
            
            
        Else
            response = MsgBox(" There was not archive table found !!" & vbCr & vbCr _
            & "Do you wish to archive the existing " & TableName1 & "  ?", vbYesNo)
            
            If response = vbYes Then
                'copy T1 to T2
                DoCmd.CopyObject , TableName2, acTable, TableName1
                 'delete Table T1  (OK as archive exists)
        	    DoCmd.DeleteObject acTable, TableName1
            Else
                Exit Sub
            End If
        End If
        
      
    Else
         MsgBox "1st table not found:  " & TableName1
         
    End If

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I don't recall a vba command like "TableExists" and I'm guessing this is a function which may somehow be opening/locking the table or causing an issue. If you close out of the mdb and get back and still are not able to delete the table, something in this function must be opening the table somehow and not releasing the lock. The docmd.copyobject shouldn't be producing a lock on the table. Try closing the mdb, opening it, and running just the docmd.deleteobject actable, TableName1 part of the code.

    Otherwise you may need to show us the function TableExists.
    Last edited by pkstormy; 01-04-08 at 18:43.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Dec 2007
    Posts
    48
    Ooops - forgot about that Function - sorry

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why are you deleting the table?

    Instead you should
    1. Empty table1
    2. INSERT data from table2 into the empty table1
    3. Empty table2
    4. Import new data into table 2


    See, no deleting
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2007
    Posts
    48

    Missing function

    Here is the function that is used to determine if the Table is missing

    Jill

    Code:
    Public Function TableExists(Db, sTable As String) As Boolean
        
        Dim tbl As DAO.TableDef
        
        TableExists = False
        
        For Each tbl In Db.TableDefs
            If tbl.Name = sTable Then TableExists = True
        Next tbl
        
        Set tbl = Nothing
    End Function

  6. #6
    Join Date
    Dec 2007
    Posts
    48
    Thanks georgev, your suggestion makes a lot more sense. ( I am new to Access and Access VBA )

    The existing code still has a problem that I need to understand, and would welcome any suggestions on why it cannot lock the Table

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    JillB,

    What happens if you close the mdb, then open the mdb, run the TableExists (create a button just to run the code), then right-click on TableName1 and try to delete it?

    Try to take the code apart step by step running each command separately. Some part has locked TableName1 (or even perhaps it was a one-time error or only happens if the code is run a 2nd time.) I'd be curious to know though if you can repeat it running it over and over again. If it works ok the first time your in the mdb but not the 2nd time your in the mdb perhaps something is not refreshing with the table definitions.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Dec 2007
    Posts
    48
    many thanks Paul. I have done all the steps your suggested, and in doing so I found 2 errors. After I had done the initial development where it worked fine, I remember that I later modified the DB to open a Form that used this table whenever the DB file was opened. duhhh!!!

    Also, I found that I should really close a table before deleting it.

    I appreciate all the help and suggestions you guys have given, and apologies for wasting your time with a stupuid mistake. The error message was correct all along

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    There was no wasting of time. It's only the expectation by some of immediate/correct answers where the problem comes in as no one is getting paid here to answer questions.

    I learn from reading posts as I'm sure others do (viva la exchange of problems/solutions.)

    Thanks for the followup post - it helps!
    Last edited by pkstormy; 01-09-08 at 17:27.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Agreed.... it's all a learning process. I learn faster from making mistakes :P
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •