Change drive letter in linked table manager

[MikeF]

Would like to change the drive letter of linked tables when necessary.
Have the following code in a module, but when I attempt to run it the macro
dialog appears.
What else needs to be done?

Thank you.
- Mike


Private Sub RelinkTables(path, db)
Dim dbs As Database
Dim tdf As TableDef
' Loop through all tables in the database.
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
' If the table has a connect string, it's a linked table.
If Len(tdf.Connect) > 0 Then
tdf.Connect = ";DATABASE=" & path & "\" & db
Err = 0
On Error Resume Next
tdf.RefreshLink ' Relink the table.
If Err <> 0 Then
End If
End If
Next tdf
End Sub

 

[Stefan Hoffmann]

hi Mike,

Would like to change the drive letter of linked tables when necessary.
Have the following code in a module, but when I attempt to run it the macro
dialog appears.
What else needs to be done?

Take a look at:

http://www.mvps.org/access/tables/tbl0009.htm


mfG
--> stefan <--

 

[MikeF]

Thank you.
There is *a lot* of code there, and would prefer - if possible - a much
simpler solution such as the one below.

To rephrase the question, what needs to be done with the following code to
make it run correctly?

Regards,
- Mike

 

[Jack Leach]

Private Sub RelinkTables(path, db)

Maybe you could try explicitly declaring the datatype of the arguments...
personally, I'd be rather nervous working with variants in a situation such
as this.

Private Sub RelinkTables(path As String, db As String)


If by any chance the arguments passing the values are not passing strings,
access may think they are any number of things, and then who knows what type
is trying to be applied to a connect string? Sounds dangerous...

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[MikeF]

Ideally, just need a simple routine that changes the path only - actually
drive letter only - of selected items in the Linked Table Manager.

Didn't earlier versions of Access do that??

- Mike

 

[Jack Leach]

I'm not sure earlier versions did (even in my 2k3 this is more or less
uncharted waters for me), but how about you put some real error handling into
the procedure so you get an actual error message instead of the macro dialog.

Let us know what the error number and description is, what line it's
throwing from, what your connect value is before and after, and we'll take it
from there?

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)