entrylevel26


I want to write a vba script to change all the links in the linked tables in an mdb file to link sql server database.

Also, if you think of a better way to do this than writing a vba script, tell me.

If you know how to do this using the upsize wizard (which mean using the same data that in the mdb files) , I will be interested to hear too. I think this is similar to the question here

http://www-new.experts-exchange.com/Microsoft/Development/MS_Access/Q_21916141.html qid=21916141&qid=21916141




Re: Linked Tables

entrylevel26


Sub execute()

Dim tdf As TableDef, db As Database
Set db = CurrentDb
db.TableDefs.Refresh

For Each tdf In db.TableDefs
With tdf

If Left$(.Connect, Len(";DATABASE=")) = ";DATABASE=" Then 'checks if this table is linked to a file

.Connect = "ODBC;DRIVER=SQL Server;SERVER=MYCOMPUTER\SQLEXPRESS;APP=Microsoft Office XP;WSID=MYCOMPUTER;DATABASE=gendbSQL;Trusted_Connection=Yes"

tdf.RefreshLink

End If
End With
Next

Set tdf = Nothing
Set db = Nothing
End Sub