Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Relinking tables (Access 2000)

    I am having a bit of a problem with some code that is supposed to refresh links. I need to do this because I am using one back end for testing and another backend for real data. gstrDataPath and gstrDataName are variables for the backend path and name. gstrLinkTables is an array holding the table names with gintLinkTables holding the number of tables in the array.

    The code is supposed to go through the list of linked tables in the array, delete each one, relink each one and then test it by opening and closing a recordset. It appears to work if there are already links in place albeit to the wrong backend but fails to relink if not.

    For intI = 1 To gintLinkTables
    strTable = gstrLinkTables(intI)
    DoCmd.DeleteObject acTable, strTable
    DoCmd.TransferDatabase acLink, "Microsoft Access", gstrDataPath & gstrDataName, acTable, strTable, strTable
    Set rs = db.OpenRecordset(strTable) 'rs already dimmed
    rs.Close
    Next

    Can anyone help me as to why the Transfer Database line fails to work if the preceding line didn't have anything to delete?

    David Parton

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Relinking tables (Access 2000)

    Do any of the tables get linked correctly?
    Have you any error checking code here?
    What may have happened is that the code may have bombed when trying to delete a non-existant linked table, in which case it would not execute the TransferDatabase statement.

    If this is the case I would replace the line:
    DoCmd.DeleteObject acTable, strTable
    with
    On Error Resume Next
    DoCmd.DeleteObject acTable, strTable
    On Error Goto 0

    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Relinking tables (Access 2000)

    I'm not sure why you're opening a recordset to check whether the link succeeded. Since you delete the links first, you should be able to simply check for the existence of the table in the current database to know whether the link was created by setting and then destroying an object reference to the table. You do need some error trapping in there to make sure you can recover from missing tables as Pat suggests and you definitely need it if you're going to try setting an object variable.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relinking tables (Access 2000)

    Thank you Pat, the added lines have made the thing work and I've added some error checking instead of the goto 0.

    Charlotte, how do I check for the existence of a table by setting an object reference to the table? I thought that's what I was doing but are you suggesting there's an easier way? Many thanks.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Relinking tables (Access 2000)

    I was talking about attempting to set a reference to the table object itself rather than trying to open a recordset on it. Here are two functions that demonstrate the method I was suggesting, one ADO and one DAO, depending on which reference you have set in your database: The ADO version will only work in Access 2000 or later.

    <pre>Public Function TableExistsADO(ByVal strTableName As String) As Boolean
    'returns a true if a table exists in the current database
    Dim objAcc As AccessObject 'holds specified object
    On Error Resume Next
    'try to set the table object
    Set objAcc = CurrentData.AllTables(strTableName)
    If Err = 0 Then
    TableExistsADO= True
    End If
    Set objAcc = Nothing
    End Function TableExistsADO(ByVal strTableName As String) As Boolean</pre>


    <pre>Function TableExistsDAO(strTableName As String) As Boolean
    ' This procedure returns True or False depending on whether
    ' the table named in strTableName exists.
    Dim dbs As DAO.Database, tdf As DAO.TableDef

    On Error Resume Next
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(strTableName)
    If Err = 3265 Then
    ' Table does not exist.
    TableExistsDAO = False
    Else
    ' Table exists.
    TableExistsDAO = True
    End If
    Err = 0
    Set tdf = Nothing
    Set dbs = Nothing
    End Function</pre>

    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relinking tables (Access 2000)

    Very many thanks Charlotte, I've incorporated your DAO code and will be testing over the weekend that the whole process works as intended. It strikes me that I can simplify part of the logic by testing first and only relinking if testing fails or if the .mdb name changes.

    One point I am concerned about. Does checking the tabledef exists actually ensure that the link has integrity? Can the link be there and yet not point to anything, causing an error when the program does try to open a recordset?

    David Parton

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Relinking tables (Access 2000)

    The answer to your last question is yes, but that isn't a situation that applies if you delete a link and then recreate it. The only way I know of to get a bad link is by moving or renaming or deleting the remote database. If you've just created a link to it, then it isn't likely that is has been moved or renamed in the meanwhile. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> On the other hand, a dropped network connection will trash the connection without invalidating the link. In that case, your recordset method won't work either. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  8. #8
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relinking tables (Access 2000)

    Yes of course. I hadn't thought that one through before posting. Doing a Dir() command somewhere in the linking routines would ensure that the target file still existed in the place the links think it is.

    Thanks for all your help.

    david

Posting Permissions

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