Change Connect Property For ODBC Connection

[SHIPP]

VERSION: ACCESS 97

ISSUE: I have approximately 400 databases on 62 servers throughout the
company. All of these databases have been home grown at the facility level.
All of them have an ODBC connection to our central server. They access the
tables of our primary accounting software. Our primary accounting software
has nearly 1,000 tables. Consequently all databases do not access all of the
tables. What I need to do is to create a generic module that will update the
links. What I anticipate is that each user will import a module into their
database and then run it. The module will need to go out and change what is
currently in the Connect field of msysObjects which is

DSN=adage_bck;DB=adage_p;HOST=adage_bck;SERV=aifx20;SRVR=aifx20;PRO=onsoctcp;

To

DSN=adage_bck_Access;DB=adage_p;HOST=adage_bck;SERV=aifx20;SRVR=aifx20;PRO=onsoctcp;

Does anyone have the code to accomplish this task? Any help would be greatly
appreciated. Thanks in advance.

NOTE: IT has removed the link table manager wizard so this needs to be done
through code.

 

[Douglas J. Steele]

You can't update MSysObjects (or at least you shouldn't), but you can easily
do it through DAO.

Type TableDetails
TableName As String
SourceTableName As String
End Type

Sub RelinkTables()

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim intLoop As Integer
Dim intToChange As Integer
Dim strConnectNew As String
Dim strConnectOld As String
Dim strCurrSourceTable As String
Dim strCurrTable As String
Dim typExistingData() As TableDetails

strConnectOld =
"ODBC;DSN=adage_bck;DB=adage_p;HOST=adage_bck;SERV=aifx20;SRVR=aifx20;PRO=onsoctcp"
strConnectNew =
"ODBC;DSN=adage_bck_Access;DB=adage_p;HOST=adage_bck;SERV=aifx20;SRVR=aifx20;PRO=onsoctcp"

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If tdfCurr.Connect = strConnectOld Then
ReDim Preserve typExistingData(0 To intToChange)
typExistingData(intToChange).TableName = tdfCurr.Name
typExistingData(intToChange).SourceTableName = tdfCurr.SourceTableName
intToChange = intToChange + 1
End If
Next tdfCurr

If intToChange > 0 Then
For intLoop = 0 To (intToChange - 1)
dbCurr.Tabledefs.Delete typExistingData(intLoop).TableName
Set tdfCurr =
dbCurr.CreateTableDef(typExistingData(intLoop).TableName)
tdfCurr.Connect = strConnectNew
tdfCurr.SourceTableName = typExistingData(intLoop).SourceTableName
dbCurr.TableDefs.Append tdfCurr
Next intLoop
End If

End Sub

 

[SHIPP]

I want to thank you first of all. I am going to apply your code to my
problem. As an aside is it possible to hae this code in a separate database
and modify it in a way that would allow the user to simply input the name of
his database and have the function change the links? What I mean is can this
module work from a secondary database without having to import it into each
of the individual databases? Thanks in advance.
--
M. Shipp

You can't update MSysObjects (or at least you shouldn't), but you can easily
do it through DAO.

Type TableDetails
TableName As String
SourceTableName As String
End Type

Sub RelinkTables()

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim intLoop As Integer
Dim intToChange As Integer
Dim strConnectNew As String
Dim strConnectOld As String
Dim strCurrSourceTable As String
Dim strCurrTable As String
Dim typExistingData() As TableDetails

strConnectOld =
"ODBC;DSN=adage_bck;DB=adage_p;HOST=adage_bck;SERV=aifx20;SRVR=aifx20;PRO=onsoctcp"
strConnectNew =
"ODBC;DSN=adage_bck_Access;DB=adage_p;HOST=adage_bck;SERV=aifx20;SRVR=aifx20;PRO=onsoctcp"

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If tdfCurr.Connect = strConnectOld Then
ReDim Preserve typExistingData(0 To intToChange)
typExistingData(intToChange).TableName = tdfCurr.Name
typExistingData(intToChange).SourceTableName = tdfCurr.SourceTableName
intToChange = intToChange + 1
End If
Next tdfCurr

If intToChange > 0 Then
For intLoop = 0 To (intToChange - 1)
dbCurr.Tabledefs.Delete typExistingData(intLoop).TableName
Set tdfCurr =
dbCurr.CreateTableDef(typExistingData(intLoop).TableName)
tdfCurr.Connect = strConnectNew
tdfCurr.SourceTableName = typExistingData(intLoop).SourceTableName
dbCurr.TableDefs.Append tdfCurr
Next intLoop
End If

End Sub

 

[Douglas J. Steele]

Sure.

Change

Set dbCurr = CurrentDb()

to

Set dbCurr = OpenDatabase("E:\folder\file.mdb")

(you can also use a UNC instead of a mapped drive, but OE is being too
helpful, and removing the quotes when I try to demonstrate that)

You should also add a

dbCurr.Close

at the end of the routine.

Just as an aside, you might find it easier to work with DSN-less
connections, since that will eliminate the need to create the DSNs on each
client. I have an introduction to the topic at
http://www.accessmvp.com/djsteele/DSNLessLinks.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I want to thank you first of all. I am going to apply your code to my
problem. As an aside is it possible to hae this code in a separate
database
and modify it in a way that would allow the user to simply input the name
of
his database and have the function change the links? What I mean is can
this
module work from a secondary database without having to import it into
each
of the individual databases? Thanks in advance.

 

[SHIPP]

Thank you so much. It works like a dream.