Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Morecambe England, Lancashire, England
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting Table Links (Access 2000)

    I have a bit of code that deletes the table links and then opens the re-link link window like this :-

    DoCmd.DeleteObject acTable, "TblProtectionProducts"
    DoCmd.DeleteObject acTable, "TblRenewal"
    DoCmd.DeleteObject acTable, "TblTaxyears"

    On Error GoTo HandleErr

    DoCmd.RunCommand acCmdLinkTables

    (in fact there are 12 tables, I've just shown the last three)
    Now that's fine and it works - providing you run it all the way through!

    If the user CLICKS ON cancel with the link window open, the linking stops OK, but without being linked, so the next time it is run, it fails, because the table it is trying to delete is no longer there!

    At the moment if that happens I have to resort to linking it manually. Can anyone PLEASE give me of a bit of code that would test to see if the table was there and skip it if it was missing. I think I will have to test each table, just in case the user selects one or two of the tables rather than "select all" in the Link window.

    Thank you again for all the help you have given in the past

    Michael Peak
    Morecambe, England

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

    Re: Deleting Table Links (Access 2000)

    Why do you delete the linked tables? If you use

    RunCommand acCmdLinkedTableManager

    (DoCmd is not necessary), the user can update the links; if (s)he cancels, the old links will remain.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Morecambe England, Lancashire, England
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Table Links (Access 2000)

    Hi Hans,

    The simple answer is I know about DoCmd.RunCommand acCmdLinkTables, I didn't know about RunCommand acCmdLinkedTableManager !!!!!

    DoCmd.RunCommand acCmdLinkTables of course leaves the old tables there and puts the new ones in as well with a 1 after it.

    I shall try it straight away - Thanks once more

    Michael

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Morecambe England, Lancashire, England
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Table Links (Access 2000)

    Hans

    Problem!!

    I don't have RunCommand acCmdLinkedTableManager as an option in my version of Access 2000 and RunCommand acCmdLinkedTable has left the old tables there and added the new ones with a 1 after them.

    Is there something missing from my version of Access?

    Michael

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

    Re: Deleting Table Links (Access 2000)

    Oops, I'm sorry. acCmdLinkedTableManager is new in Access 2002 (the version I am using)

    You can turn off error handling temporarily when deleting the links:

    On Error Resume Next
    DoCmd.DeleteObject acTable, "TblProtectionProducts"
    DoCmd.DeleteObject acTable, "TblRenewal"
    DoCmd.DeleteObject acTable, "TblTaxyears"
    On Error GoTo 0

    This will let the code continue if one or more of the tables doesn't exist.

    If you have error handling with On Error GoTo <labelname> in your code, use that instead of On Error GoTo 0.

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Morecambe England, Lancashire, England
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Table Links (Access 2000)

    Dear Hans

    Great - works perfectly I just linked to one table by hand. Ran the code and it linked to all the tables.wiith no error messages

    I'm not sure if I want to get so involved, but is there a way of linking without having to use the re-link Window? Or of making sure that all the table are selected without the user having a choice?

    There is no switches or what ever to use - say acCmdLinkedTable (All) or something is there?

    Thanks

    Michael

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

    Re: Deleting Table Links (Access 2000)

    Why do you want to delete and recreate the links?

    If you want to link tables programmatically, check out MSKB articles ACC2000: How to Relink Back-End Tables with the Common Dialog Control and MOD2000: How to Refresh Links in a Run-Time Application.

  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Table Links (Access 2000)

    I had a need very much like the person who started this thread. The reason I needed to remove the links and relink the tables is because the dBase database that I link to changes its name every year. I furnished the user with a combo box to select the year and then the following code deletes and recreates the link using the year selected in the combo box. I too was having a problem when the code tried to delete a link that no longer existed. Hans furnished the error handler portion of the following code that works just great.

    On Error GoTo ErrHandler
    DoCmd.DeleteObject acTable, "tblASTU"
    DoCmd.TransferDatabase acLink, "dbase IV", "pwcs-sisappssasixpdatafile", acTable, "ASTU" & Forms!frmConsolidationLink!cmbSchYear.Column(1) & "D01.dbf", "tblASTU"
    Exit Sub

    ErrHandler:
    Select Case Err
    Case 7874 'Table not found - continue
    Resume Next
    Case Else
    MsgBox Err.Description, vbExclamation
    End Select
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  9. #9
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Morecambe England, Lancashire, England
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Table Links (Access 2000)

    Hi Judy

    I started this link and the reason I want to be able to link and unlink is fairly straight forward. We have a Backend Database just of tables (there are 12 tables) on the office server and then three users on their own machines each with a copy of the main database. Now I can relink them by hand if the links get broken, which is not very often, but I wanted a way that each of the uses could do it themselves, without getting me to go it. So a button to delete all the table links, then to relink them was ideal and it worked well. Which brings me to the error checking which I asked Hans for advise.

    We re also thinking of selling the database and of course if we do, we can't expect customers to have to start fiddling around going the linking on their machines. It's going be bad enough getting them to put the tables on their servers and linking them to to their own machines by browsing, without having to do it manually!!

    By the way thank you for an idea that came out of your code

    ErrHandler:
    Select Case Err
    Case 7874 'Table not found - continue
    Resume Next
    Case Else
    MsgBox Err.Description, vbExclamation
    End Select

    I wanted to find a way of making sure that they had clicked on "Select All". If they hadn't then looking for case 7874 will see if a table is missing and make then do it again!

    Thanks for the interest,

    Michael Peak

    I thought you might be interested in a bit of the code, which I will now alter to get rid of the rather crude opening and closing a table to see if it is there!

    On Error Resume Next

    DoCmd.DeleteObject acTable, "TblPensionFunds"
    DoCmd.DeleteObject acTable, "TblPensionProducts"
    DoCmd.DeleteObject acTable, "TblProperty"
    DoCmd.DeleteObject acTable, "TblProtectionFunds"
    DoCmd.DeleteObject acTable, "TblProtectionProducts"
    DoCmd.DeleteObject acTable, "TblRenewal"
    DoCmd.DeleteObject acTable, "TblTaxyears"

    On Error GoTo HandleErr

    RunCommand acCmdLinkTables
    DoCmd.OpenTable "TblTaxYears"
    On Error GoTo HandleErr
    DoCmd.Close acTable, "Tbltaxyears"
    MsgBox "Database Re-Linked", , " Status"

    ExitHere:
    DoCmd.Close
    DoCmd.OpenForm "switchboard"
    Exit Sub

    HandleErr:

    MsgBox "Link process did not complete" & vbCrLf _
    & "Did you forget to select all the tables or click cancel?", , "Link Error"

    Exit Sub
    End Sub

Posting Permissions

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