Re: Renaming linked tables

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

"Douglas J Steele" wrote:

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?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"TLuebke" <TLuebke@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D66B942A-EED6-40E5-9773-6270ED3622D7@xxxxxxxxxxxxxxxx
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" wrote:

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.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"TLuebke" <TLuebke@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C051BF3A-F37A-4AC7-B940-5200BE88E088@xxxxxxxxxxxxxxxx
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









.

Relevant Pages

  • Re: cnn.execute problem with dates
    ... > Doug Steele, Microsoft Access MVP ... >> based upon the src tbl info into a temporary tbl. ... >> Dim rsSrc As ADODB.Recordset ... >> Dim strDelSQL As String ...
    (microsoft.public.access.formscoding)
  • Sum in a dynamic query
    ... Private Sub Form_Open ... Dim db As Database, Tbl As TableDef ... Dim db As Database, qd As QueryDef, ctl As Control, s As String, Item As ...
    (comp.databases.ms-access)
  • Re: - How do I rename print-to-file filename at runtime?
    ... Is there a way to rename printer file port name at runtime BEFORE (not ... > pFrom As String ... > "SHFileOperationA" (lpFileOp As SHFILEOPSTRUCT) As Long ...
    (microsoft.public.vb.winapi)
  • Re: find and display files
    ... "Douglas J. Steele" wrote: ... Dim strFilter As String ... Doug Steele, Microsoft Access MVP ...
    (microsoft.public.access.formscoding)
  • Re: Link tables on strat up!
    ... Public Function LinkTable(LocalTable As String, ... Dim db As DAO.Database, tbl As DAO.TableDef ... If SourceDatabase = db.Name Then ... That will always look for the back end data file in the same folder as the ...
    (microsoft.public.access.formscoding)