Refresh links in ACCDE

[Francois Houde]

Hi everybody, I have a problem with the code below. It works fine when it is
in a accdb but when I create an ACCDE it doesn't work. Any idea on how to
solve this issue ? I'm trying to connect to a different sql database (test,
pre-production, production depending on which one the user want to access)

Thanks in advance

François Houde

Dim rVersion As String
Dim rConnect As String
Dim TableDef As Object

rVersion = WhichVersion()
Me.Caption = Trim(Me.Caption) & " (" & rVersion & ")"
If rVersion = "Pre-production" Then
rConnect = "ODBC;DSN=DSN_PreProd;Description=DSN_PreProd;APP=2007
Microsoft Office system;DATABASE=PreProd;Trusted_Connection=Yes"
End If
If rVersion = "Production" Then
rConnect =
"ODBC;DSN=DSN_Production;Description=DSN_Production;APP=2007 Microsoft Office
system;DATABASE=Production;Trusted_Connection=Yes"
End If
If rVersion = "Test" Then
rConnect = "ODBC;DSN=DSN_Test;Description=DSN_Test;APP=2007
Microsoft Office system;DATABASE=Test;Trusted_Connection=Yes"
End If

For Each TableDef In CurrentDb.TableDefs
If TableDef.Connect <> "" Then
TableDef.Connect = rConnect
TableDef.Refreshlink
End If
Next

 

[Douglas J. Steele]

What happens when you try? Do you get an error message? If so, what's the
error message?

 

[AccessVandal via AccessMonster.com]

I see a problem with reserved name "TableDef" and you had declared it as an
object. Shouldn't it be

Dim oTdf As TableDef

Don't forget to destroy or close it.

 

[Francois Houde]

Don't know why, but I compile the vba Code, compact the database and the
recreate the ACCDE. Then It Worked fine.

Thanks all

 

[Tony Toews [MVP]]

I see a problem with reserved name "TableDef" and you had declared it as an
object. Shouldn't it be

Dim oTdf As TableDef

Good catch.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/