Updating Linked Tables Access 2000 1

[turtlemaster]

I have a split 2000 database and want to refresh the table links through code. I researched how to do this on the web and came up with this code.

Dim i As Integer
For i = 0 To CurrentDb.TableDefs.count - 1
If Not CurrentDb.TableDefs(i).Connect = "" Then
CurrentDb.TableDefs(i).Connect = "MS Access;DATABASE=" & txtPath & ";PWD=" & txtPassword
CurrentDb.TableDefs(i).RefreshLink
End If
Next i

For some reason it won't update the tabledef.connect property. I try to set it to something else and it won't change. No errors are generated but nothing happens. Any suggestions. (don't want to use linked table manager)

Thanks,
Nick

 

[RickSpr]

I think you'll need to create a TableDef variable and assign TableDefs(i) to it. Every time you refer to an item in the TableDefs collection, you get a fresh copy of the real TableDef. Thus, when you assign a new connect string, it actually gets assigned, but only to the copy, which immediately disappears when the statement ends. In the next statement, you get still another copy and call its RefreshLink method, but this copy doesn't have your connect string in it any more.

Also, I have found the documentation misleading about how to construct a connect string. What works for me is &quot;;DATABASE=<database path>&quot; (I don't need the password in my application). Rick Sprague

 

[turtlemaster]

Rick,

Your definently on to something. I tried your suggestion but not it tells me that the object variable is not set -- on the next line of code even!! It doesn't make sense to me Any other sugestions.

Dim i As Integer, tbdef As DAO.TableDef
For i = 0 To CurrentDb.TableDefs.count - 1
If Not CurrentDb.TableDefs(i).Connect = &quot;&quot; Then
Set tbdef = CurrentDb.TableDefs(i)
' Crashes on next that object not set but I just set it!!
tbdef.Connect = &quot;MS Access;DATABASE=&quot; & txtPath & &quot;;PWD=&quot; & txtPassword
tbdef.RefreshLink
End If
Next i

Nick

 

[ivoef]

Hi Nick,

try this function. I use it and it works. I do not use any password and I have also admnistrator rigths. The tables have to have an identical structure.

Ivo

Private Function refreshLinks(strFileName As String) As Boolean
' Refresh links to the supplied database. Return True if successful.

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef

' Loop through all tables in the database.
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
' If the table has a connect string, it's a linked table.
If Len(tdf.Connect) > 0 Then
tdf.Connect = &quot;;DATABASE=&quot; & strFileName
Err = 0
On Error Resume Next
tdf.RefreshLink ' Relink the table.
If Err <> 0 Then
refreshLinks = False
MsgBox &quot;Error..... '&quot; & tdf.Name & &quot;'&quot;, vbOKOnly Or vbExclamation, &quot;Error&quot;
Exit Function
End If
End If
Next tdf

refreshLinks = True ' Relinking complete.

End Function

 

[turtlemaster]

That did it!!! Thanks so much. So wierd though it does the same thing that my other code did. Oh well -- I must of made a stupid error (wouldn't be the first time)

Thank-you SOOOO much!!

 

[RickSpr]

Actually, I can explain it. Like the TableDefs collection, CurrentDb() returns a reference to a copy the current Database object, one that disappears as soon as the statement has executed. Each time you use CurrentDb(), you get a different copy. So what happened is that after the Set statement, the CurrentDb copy was destroyed, along with the copy of the TableDef object that tbdef referred to.

I should have caught this just as I did the TableDefs reference, but sometimes you can get away with using CurrentDb(), such as when you're just executing a SQL statement.

The general rule is, only use CurrentDb(), TableDefs, or QueryDefs (or most other DAO properties) to set an object variable, not to refer to their properties or methods. You can use the shortcut if you can accomplish everything you need to do in one VBA statement, but using object variables will always work, so when in doubt use object variables. Rick Sprague