DataBase - Refresh links in ACCDE

Asked By Francois Houde

21-Oct-09 01:31 PM

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 does not work.  Any idea on how to
solve this issue ? I am 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 =
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

21-Oct-09 01:46 PM

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

22-Oct-09 05:36 AM

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.


--
Please Rate the posting if helps you.

Message posted via http://www.accessmonster.com

22-Oct-09 03:00 PM

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

Thanks all

22-Oct-09 11:11 PM

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/