Changing Database Links to a newer copy

[John Ortt]

Hi Everyone,

This is a follow-on message from my previous post entitled:

"Can I run an update Query on the MSysObjects Table?"

I have now decided (been persuaded) that this is not the best way to do what
I want to achieve.

I have looked at http://www.mvps.org/access/tables/tbl0009.htm in the hope
of figuring out how to apply the code to my application but I fear it is way
above my knowledge level.

Essentially I wish to swap the links between two databases. The code below
shows a converted macro which did this for a single table, but there could
be dozens of tables and I don't want to repeat the code for each one. I
also want the code to be future proof and search out each table which needs
changing to ensure that new tables which are added are automatically
included.

If anybody can give me any pointers I would be very grateful.

Thankyou,

John.

CODE FOLLOWS:
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Function LinkTo1()

DoCmd.DeleteObject acTable, "1"
DoCmd.TransferDatabase acLink, "Microsoft Access", "H:\Temp\New
Folder\1\DATABASE.mdb", acTable, "1", "1", False

End Function

Function LinkTo2()

DoCmd.DeleteObject acTable, "1"
DoCmd.TransferDatabase acLink, "Microsoft Access", "H:\Temp\New
Folder\2\DATABASE.mdb", acTable, "1", "1", False

End Function

 

[Nikos Yannacopoulos]

John,

Instead of deleting and re-linking each table, you could just change the
links of existing tables without deleting them (assuming the "old" and
"new" back ends have the exact same tables).

Here's a function in VBA that will do that for each linked table,
without relying on table names:

Function change_links()
Dim db As DAO.Database
Dim tbl As DAO.TableDef

Set db = CurrentDb()

oldBE = "H:\Temp\New Folder\1\DATABASE.mdb"
newBE = "H:\Temp\New Folder\2\DATABASE.mdb"

For i = 0 To db.TableDefs.Count - 1
tbln = db.TableDefs(i).Name
Set tbl = db.TableDefs(tbln)
lnk = tbl.Connect
If Left(lnk, 9) = ";DATABASE" Then
lnk = Replace(lnk, oldBE, newBE)
tbl.Connect = ""
tbl.Connect = lnk
tbl.RefreshLink
End If
Next

End Function

All that's required is an appropriate DAO reference. To check and add if
required: while in the VBA editor go Tools > References and look for a
"Microsoft DAO 3.X Object Library" among the checked ones at the top of
the list. If you don't see one, then scroll down to find it and check
it; "appropriate" is 3.51 for A97, 3.6 for A2K or later.

HTH,
Nikos

 

[John Ortt]

Thankyou Nikos,

That looks like just what I needed.

John

 

[John Ortt]

I tried using the code you suggested Nikos but there were a number of
errors.

By adding extra variables I managed to get the code to run up until the
"REPLACE" comand but then it says "Sub or Function not defined"

I have checked my DAO version and I am on 3.51 which you said is correct for
A97.

Thanks again,

John

CODE FOLLOWS
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Function change_links12()
Dim db As DAO.DATABASE
Dim tbl As DAO.TableDef
Dim oldBE As String
Dim newBE As String
Dim i As Integer
Dim tbln As String
Dim lnk As String

Set db = CurrentDb()

oldBE = "H:\Access\Local\New Folder\1\DATABASE.mdb"
newBE = "H:\Access\Local\New Folder\2\DATABASE.mdb"

For i = 0 To db.TableDefs.Count - 1
tbln = db.TableDefs(i).Name
Set tbl = db.TableDefs(tbln)
lnk = tbl.Connect
If Left(lnk, 9) = ";DATABASE" Then
lnk = Replace(lnk, oldBE, newBE)
tbl.Connect = ""
tbl.Connect = lnk
tbl.RefreshLink
End If
Next

End Function

 

[Tom Wickerath]

Hi John,

The Replace function was introduced with Access 2000, which explains the error message when you
attempt to compile it using Access 97:

ACC2000: How to Replace a String with Another String
http://support.microsoft.com/?id=210465


Try adding the Replace function shown in this KB article:

ACC: How to Replace a String with Another String
http://support.microsoft.com/?id=109330


Also, I recommend using the UNC convention (\\Server\Share) instead of a mapped drive letter like
H:\.

Tom
_________________________________

I tried using the code you suggested Nikos but there were a number of
errors.

By adding extra variables I managed to get the code to run up until the
"REPLACE" comand but then it says "Sub or Function not defined"

I have checked my DAO version and I am on 3.51 which you said is correct for
A97.

Thanks again,

John

CODE FOLLOWS
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Function change_links12()
Dim db As DAO.DATABASE
Dim tbl As DAO.TableDef
Dim oldBE As String
Dim newBE As String
Dim i As Integer
Dim tbln As String
Dim lnk As String

Set db = CurrentDb()

oldBE = "H:\Access\Local\New Folder\1\DATABASE.mdb"
newBE = "H:\Access\Local\New Folder\2\DATABASE.mdb"

For i = 0 To db.TableDefs.Count - 1
tbln = db.TableDefs(i).Name
Set tbl = db.TableDefs(tbln)
lnk = tbl.Connect
If Left(lnk, 9) = ";DATABASE" Then
lnk = Replace(lnk, oldBE, newBE)
tbl.Connect = ""
tbl.Connect = lnk
tbl.RefreshLink
End If
Next

End Function
_________________________________


John,

Instead of deleting and re-linking each table, you could just change the
links of existing tables without deleting them (assuming the "old" and
"new" back ends have the exact same tables).

Here's a function in VBA that will do that for each linked table,
without relying on table names:

Function change_links()
Dim db As DAO.Database
Dim tbl As DAO.TableDef

Set db = CurrentDb()

oldBE = "H:\Temp\New Folder\1\DATABASE.mdb"
newBE = "H:\Temp\New Folder\2\DATABASE.mdb"

For i = 0 To db.TableDefs.Count - 1
tbln = db.TableDefs(i).Name
Set tbl = db.TableDefs(tbln)
lnk = tbl.Connect
If Left(lnk, 9) = ";DATABASE" Then
lnk = Replace(lnk, oldBE, newBE)
tbl.Connect = ""
tbl.Connect = lnk
tbl.RefreshLink
End If
Next

End Function

All that's required is an appropriate DAO reference. To check and add if
required: while in the VBA editor go Tools > References and look for a
"Microsoft DAO 3.X Object Library" among the checked ones at the top of
the list. If you don't see one, then scroll down to find it and check
it; "appropriate" is 3.51 for A97, 3.6 for A2K or later.

HTH,
Nikos
_________________________________


Hi Everyone,

This is a follow-on message from my previous post entitled:

"Can I run an update Query on the MSysObjects Table?"

I have now decided (been persuaded) that this is not the best way to do what
I want to achieve.

I have looked at http://www.mvps.org/access/tables/tbl0009.htm in the hope
of figuring out how to apply the code to my application but I fear it is way
above my knowledge level.

Essentially I wish to swap the links between two databases. The code below
shows a converted macro which did this for a single table, but there could
be dozens of tables and I don't want to repeat the code for each one. I
also want the code to be future proof and search out each table which needs
changing to ensure that new tables which are added are automatically
included.

If anybody can give me any pointers I would be very grateful.

Thankyou,

John.

CODE FOLLOWS:
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Function LinkTo1()

DoCmd.DeleteObject acTable, "1"
DoCmd.TransferDatabase acLink, "Microsoft Access", "H:\Temp\New
Folder\1\DATABASE.mdb", acTable, "1", "1", False

End Function

Function LinkTo2()

DoCmd.DeleteObject acTable, "1"
DoCmd.TransferDatabase acLink, "Microsoft Access", "H:\Temp\New
Folder\2\DATABASE.mdb", acTable, "1", "1", False

End Function

 

[John Ortt]

Thanks for the advice Tom I'll give it a try.

John

Hi John,

The Replace function was introduced with Access 2000, which explains the error message when you
attempt to compile it using Access 97:

ACC2000: How to Replace a String with Another String
http://support.microsoft.com/?id=210465


Try adding the Replace function shown in this KB article:

ACC: How to Replace a String with Another String
http://support.microsoft.com/?id=109330


Also, I recommend using the UNC convention (\\Server\Share) instead of a mapped drive letter like
H:\.

Tom
_________________________________

I tried using the code you suggested Nikos but there were a number of
errors.

By adding extra variables I managed to get the code to run up until the
"REPLACE" comand but then it says "Sub or Function not defined"

I have checked my DAO version and I am on 3.51 which you said is correct for
A97.

Thanks again,

John

CODE FOLLOWS
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Function change_links12()
Dim db As DAO.DATABASE
Dim tbl As DAO.TableDef
Dim oldBE As String
Dim newBE As String
Dim i As Integer
Dim tbln As String
Dim lnk As String

Set db = CurrentDb()

oldBE = "H:\Access\Local\New Folder\1\DATABASE.mdb"
newBE = "H:\Access\Local\New Folder\2\DATABASE.mdb"

For i = 0 To db.TableDefs.Count - 1
tbln = db.TableDefs(i).Name
Set tbl = db.TableDefs(tbln)
lnk = tbl.Connect
If Left(lnk, 9) = ";DATABASE" Then
lnk = Replace(lnk, oldBE, newBE)
tbl.Connect = ""
tbl.Connect = lnk
tbl.RefreshLink
End If
Next

End Function
_________________________________

news:%[email protected]...

<snipped>

 

[John Ortt]

Thanks again Tom, It's working perfectly...

I had a little bit of trouble at first as I put the "Replace" code in a
separate module but this was sorted by moving the code into the same module
as the rest of the code.

Now to move it from my test database to the live copy..wish me luck....

John

Just for interest, the code now looks like this:

//////////////////////

Option Compare Database
Option Explicit

Function change_links21()
Dim db As DAO.DATABASE
Dim tbl As DAO.TableDef
Dim oldBE As String
Dim newBE As String
Dim i As Integer
Dim tbln As String
Dim lnk As String

Set db = CurrentDb()

oldBE = "H:\Access\Local\New Folder\2\DATABASE.mdb"
newBE = "H:\Access\Local\New Folder\1\DATABASE.mdb"

For i = 0 To db.TableDefs.Count - 1
tbln = db.TableDefs(i).Name
Set tbl = db.TableDefs(tbln)
lnk = tbl.Connect
If Left(lnk, 9) = ";DATABASE" Then
lnk = Replace(lnk, oldBE, newBE)
tbl.Connect = ""
tbl.Connect = lnk
tbl.RefreshLink
End If
Next

End Function

Function Replace(ByVal Valuein As String, ByVal WhatToReplace As
String, ByVal Replacevalue As String) As String
Dim Temp As String, P As Long
Temp = Valuein
P = InStr(Temp, WhatToReplace)
Do While P > 0
Temp = Left(Temp, P - 1) & Replacevalue & Mid(Temp, P +
Len(WhatToReplace))
P = InStr(P + Len(Replacevalue), Temp, WhatToReplace, 1)
Loop
Replace = Temp
End Function

 

[Tom Wickerath]

Hi John,

wish me luck....

Good Luck!

I recommend adding an error handler to your functions, and close the database object that you
opened in the change_links21 function. The rest of your code goes where I am showing the )
symbols.

Tom


Function change_links21()
On error goto ProcError
:
:
:
ExitProc:
On Error Resume Next
db.Close
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in change_links21 Function..."
Resume ExitProc
End Function

__________________________________


Thanks again Tom, It's working perfectly...

I had a little bit of trouble at first as I put the "Replace" code in a
separate module but this was sorted by moving the code into the same module
as the rest of the code.

Now to move it from my test database to the live copy..wish me luck....

John

Just for interest, the code now looks like this:

//////////////////////

Option Compare Database
Option Explicit

Function change_links21()
Dim db As DAO.DATABASE
Dim tbl As DAO.TableDef
Dim oldBE As String
Dim newBE As String
Dim i As Integer
Dim tbln As String
Dim lnk As String

Set db = CurrentDb()

oldBE = "H:\Access\Local\New Folder\2\DATABASE.mdb"
newBE = "H:\Access\Local\New Folder\1\DATABASE.mdb"

For i = 0 To db.TableDefs.Count - 1
tbln = db.TableDefs(i).Name
Set tbl = db.TableDefs(tbln)
lnk = tbl.Connect
If Left(lnk, 9) = ";DATABASE" Then
lnk = Replace(lnk, oldBE, newBE)
tbl.Connect = ""
tbl.Connect = lnk
tbl.RefreshLink
End If
Next

End Function

Function Replace(ByVal Valuein As String, ByVal WhatToReplace As
String, ByVal Replacevalue As String) As String
Dim Temp As String, P As Long
Temp = Valuein
P = InStr(Temp, WhatToReplace)
Do While P > 0
Temp = Left(Temp, P - 1) & Replacevalue & Mid(Temp, P +
Len(WhatToReplace))
P = InStr(P + Len(Replacevalue), Temp, WhatToReplace, 1)
Loop
Replace = Temp
End Function