Renaming linked tables

[Guest]

How would I go about renaming linked tables. I'm using this code to link them
with "tbl" and want to rename them via an older convention "tblo"
The code links ok but totally skips the renaming part (except the status bar
message works, showing the last record)

Thanks,

Todd



Sub RelinkTables()
Dim myuserid, mypswrd, mydsn As String

myuserid = InputBox("Enter Replicated Database UserID", "UserID")
mypswrd = InputBox("Enter Replicated Database Password", "Password")
mydsn = InputBox("Enter ODBC Data Source Name of Replicated Database",
"DSN", "NHCC Replicated")
renameyn = MsgBox("Do you want to rename the table to the old format?",
vbYesNo, "Rename Tables")

Set dbs = CurrentDb
Set temprst = dbs.OpenRecordset("select * from LinkTables")

If temprst.EOF = False Then
temprst.MoveLast
temprst.MoveFirst
End If

'links tables from replicated database
For X = 1 To temprst.RecordCount
tbl = temprst!tablename
tblo = temprst!tablenameold

'delete table that is currently linked old and new
For Each tdf In dbs.tabledefs
If tdf.Name = tbl Then dbs.tabledefs.Delete tdf.Name
Next tdf
For Each tdf In dbs.tabledefs
If tdf.Name = tblo Then dbs.tabledefs.Delete tdf.Name
Next tdf

'relink table with
DoCmd.TransferDatabase acLink, "ODBC Database", _
"ODBC;DSN=" & mydsn & ";UID=" & myuserid & ";PWD=" & mypswrd & _
";LANGUAGE=us_english;", acTable, tbl, tbl, False, True

DoCmd.Echo True, "linking table: " & tbl
temprst.MoveNext

Next X

' Rename tables if needed
If renameyn = vbYes Then
For Each tdf In dbs.tabledefs
If tdf.Name = tbl Then
tdf.Name = Rename(tdf.Name, "", tblo)
End If
DoCmd.Echo True, "Renaming table from: " & tbl & " to: " & tblo
Next tdf

End If

temprst.Close
dbs.Close

MsgBox "Tables successfully relinked", vbOKOnly

End Sub

 

[Douglas J. Steele]

I suspect your problem is this:

If tdf.Name = tbl Then
tdf.Name = Rename(tdf.Name, "", tblo)
End If

That's only going to attempt to rename the table if the name of the is
whatever's currently stored in the variable tbl.

 

[Guest]

Douglas,
I'll like to rename each table right after the linking for each one. Then
move to the next record. Any suggestions for a better way?

Thanks,

 

[Douglas J Steele]

Try putting

dbs.TableDefs(tbl).Name = Rename(tbl, "", tblo)

after your TransferDatabase statement.

FWIW, the only declaration showing in the code you posted doesn't do what
probably you think it does.

Dim myuserid, mypswrd, mydsn As String

declares 3 variables, but only one of the (mydsn) is declared as a string.
The other two, since they have no data type associated with them, are
declared as variants. You can't "short circuit" the declaration in Access
(even though you can in some other languages). To have all 3 variables as
strings, you must use:

Dim myuserid As String, mypswrd As String, mydsn As String

BTW, where have you declared such variables as dbs, temprst, tbl and tblo?

 

[Guest]

Thanks Doug,
I got this snippet from someone else and have been using as is. I'll do
the suggested house keeping on it.

Todd