Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    test for open table (97 2)

    Good Morning,
    Is there anyone to test to see if a table is open before you try to execute a delete of the records? I have to delete the records out of a table every week and repopulate it. My problem is my code hangs if someone has the table open. I would like to be able to check and see if the table is open and if it is stop the code.
    Thanks
    Cathy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: test for open table (97 2)

    If you use a delete query, you could set its RecordLocks property to All Records. The query will refuse to run if someone has the table open.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: test for open table (97 2)

    Hans,
    I am not using a delete query, here is my line of code
    dbs.Execute "delete * from NFSNameandAddress101"
    Cathy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: test for open table (97 2)

    Try

    dbs.Execute "delete * from NFSNameandAddress101", dbFailOnError

    This should cause Access to generate an error message if the SQL statement fails.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: test for open table (97 2)

    Hans
    Is there anyway to ID who has the table open?
    Cathy

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: test for open table (97 2)

    You can find out who has the database as a whole open, but I don't know of any way to determine who has a table open.

  7. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: test for open table (97 2)

    Hans.
    I code worked as I need it to work. Ir shows me a runtime error 3218 and gives me a machine number, which is the machine number of the users hold the table open. How do I grad that machine number so I can place it in an error log.
    Thanks
    Cathy

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: test for open table (97 2)

    Sorry, I don't understand. I don't know what 'grad' means in this context (English is not my native language). Could you explain?

  9. #9
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: test for open table (97 2)

    Hans I am sorry my typing error.

    The code worked as I need it to work. Access shows me a runtime error 3218 and gives me a machine number in the error message.. How do I grab that machine number so I can place it in an error log.

    Thanks again for all your help, you are the best.
    Cathy
    Thanks

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: test for open table (97 2)

    Perhaps you can extract the machine name by using an error handler:

    Sub ThisOrThat()
    Dim strError As String
    Dim dbs As DAO.Database
    On Error GoTo ErrHandler

    ...
    dbs.Execute "delete * from NFSNameandAddress101", dbFailOnError
    ...

    ExitHandler
    ' Release object variables
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    If Err = 3218 Then
    ' Assign error message to string variable
    strError = Err.Description
    ' Code to parse strError goes here
    ...
    Else
    MsgBox Err.Description, vbExclamation
    End If
    Resume ExitHandler
    End Sub

    I don't know exactly what the error message looks like, so I can't give detailed instructions for parsing the string. If you need help, please post an example of the error message.

Posting Permissions

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