Relinking tables (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

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

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

Reply | Quote

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

Reply | Quote

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. 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.

Reply | Quote

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

Reply | Quote