Linking Ms Access to Sql Server
Hello Experts!
I have create a macro called AutoExec to run the function below:
Public Function SqlLinker()
Dim db As Database
Set db = CurrentDb
Dim tdef As TableDef
Dim constr As Variant
constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=USER\SQLExpress;DA
For Each tdef In db.TableDefs
If InStr(tdef.Connect, "ODBC") Then
tdef.Connect = constr
tdef.RefreshLink
End If
Next
MsgBox "Re link completed Successfully", vbOKOnly, "CA Premier Accounting Package"
End Function
Well the function links the tables in SQl Server at runtime and if the ODBC 17 path is correct a message with Re link completed Successfully pop up. All is well as long as I continue using my laptop the problem comes up if I install the same database on a different server client stand alone network not connect with my laptop because the new path is supposed to be:
constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=PETER\SQLExpress;D
Now how best I can be changing the path below to be reference the correct server name? For example if the same name changes to CHRIS is there a way to alter the path below:
constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=USER\SQLExpress;DA
Example where there is USER change to CHRIS,the challenge here is how to change the code manually because we always send an accde format to clients. Some are suggesting to put this string below in table and use dlookup to reference it but I'm not sure how:USER\SQLExpress;DATABA
Any help out there? how are deploying this sql server to different clients? I'm sure some could have faced this challenge.
Kindly see how you can assist
Regards
Chris
I have create a macro called AutoExec to run the function below:
Public Function SqlLinker()
Dim db As Database
Set db = CurrentDb
Dim tdef As TableDef
Dim constr As Variant
constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=USER\SQLExpress;DA
For Each tdef In db.TableDefs
If InStr(tdef.Connect, "ODBC") Then
tdef.Connect = constr
tdef.RefreshLink
End If
Next
MsgBox "Re link completed Successfully", vbOKOnly, "CA Premier Accounting Package"
End Function
Well the function links the tables in SQl Server at runtime and if the ODBC 17 path is correct a message with Re link completed Successfully pop up. All is well as long as I continue using my laptop the problem comes up if I install the same database on a different server client stand alone network not connect with my laptop because the new path is supposed to be:
constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=PETER\SQLExpress;D
Now how best I can be changing the path below to be reference the correct server name? For example if the same name changes to CHRIS is there a way to alter the path below:
constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=USER\SQLExpress;DA
Example where there is USER change to CHRIS,the challenge here is how to change the code manually because we always send an accde format to clients. Some are suggesting to put this string below in table and use dlookup to reference it but I'm not sure how:USER\SQLExpress;DATABA
Any help out there? how are deploying this sql server to different clients? I'm sure some could have faced this challenge.
Kindly see how you can assist
Regards
Chris
Thank you so much Dale I have looked at the article it is good if I was dealing with one client I would try, but I want to work with the same code above, though may the other way around it could be changing the path manually but the problem is how to compile it?
Regards
Chris
Regards
Chris
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Chris:
Are you trying to get your code modified to handle a change in ODBC connection string parameters?
Do you need a pop-up form to enter the new connection string parameters and then re-link your SQL Server tables?
All of this is easy to do, IF you have a way of changing your connection string parameters.
How are you doing that?
Are you trying to get your code modified to handle a change in ODBC connection string parameters?
Do you need a pop-up form to enter the new connection string parameters and then re-link your SQL Server tables?
All of this is easy to do, IF you have a way of changing your connection string parameters.
How are you doing that?
Take a look at my Linked Table Manager for Access and SQL Server, located in this article. It is a free Access Add-in and I use it in place of the built-in LTM for Access 2007 - 2016. Makes it very easy to change the connection string to SQL Server databases.
Dale