This is code I use for relinking tables and querydefs to MS SQL Server.
Your connection string will vary. You could pass in the dynamic values
as parameters to the function.
Function RelinkODBC()
Dim td As DAO.TableDef
Dim qd As DAO.QueryDef
Dim db As DAO.Database
Dim strConnect As String
strConnect = "ODBC;Driver={SQL
Server};Server=YourServer;Database=YourDB;Uid=YourID;Pwd=YourPWD"
Set db = CurrentDb
For Each td In db.TableDefs
If Left(td.Connect, 4) = "ODBC" Then
Debug.Print td.Name
td.Connect = strConnect
td.Attributes = DB_ATTACHSAVEPWD
td.RefreshLink
End If
Next
For Each qd In db.QueryDefs
If Left(qd.Connect, 4) = "ODBC" Then
Debug.Print qd.Name
qd.Connect = strConnect
End If
Next
Set td = Nothing
Set qd = Nothing
Set db = Nothing
End Function
Duane Hookom
MS Access MVP
--- In MS_Access_Professionals@yahoogroups.com, "Coach K"
<sk8ofmind@...> wrote:
>
> I have a number of pass thru queries throughout my apps.
> I query against a DB2 back-end. Our mainframe requires a password
change every 90days. Until yesterday I was able to get around that but
=now I have to change the password, and now go into each and every query
to modify the password in the connect property.
>
> Is there a way to make a function with a connect string and store the
UID and PWD in a table and therefore only have to change the table.
> what do you guys do.
>
> Thanks for the help.
>
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar