Coding to create linked tables

[Tom Ellison]

Please consider this VBA code:

Dim MyDatabase As Database, i As Integer

Set MyDatabase = OpenDatabase("D:\NWNKBE.mdb")
For i = 0 To MyDatabase.TableDefs.Count - 1
If Left(MyDatabase.TableDefs(i).Properties(0), 4) <> "MSys" Then
Debug.Print MyDatabase.TableDefs(i).Name
MyDatabase.TableDefs(i).Name = "NK" &
MyDatabase.TableDefs(i).Name
CurrentDb.TableDefs.Append MyDatabase.TableDefs(i)
End If
Next i

There is a surprising result to this code. The table names in the NWNKBE
database are actually being changed! I had expected the local copy of it to
be changed, not the original.

What I'd like to do is to create a set of uniquely named linked tables from
a set of 3 databases, all verisons of Northwind. So, they all have the same
set of tables, names and all. I just want to have uniquely named links to
them. I can do that manually quite easily.

Thanks for your advice on this. I'll be looking at all the books for
information, too.

Tom Ellison

 

[Wolfgang Kais]

Hello Tom.

Please consider this VBA code:

Dim MyDatabase As Database, i As Integer

Set MyDatabase = OpenDatabase("D:\NWNKBE.mdb")
For i = 0 To MyDatabase.TableDefs.Count - 1
If Left(MyDatabase.TableDefs(i).Properties(0), 4) <> "MSys" Then
Debug.Print MyDatabase.TableDefs(i).Name
MyDatabase.TableDefs(i).Name = "NK" &
MyDatabase.TableDefs(i).Name
CurrentDb.TableDefs.Append MyDatabase.TableDefs(i)
End If
Next i

There is a surprising result to this code. The table names in the NWNKBE
database are actually being changed! I had expected the local copy of it
to be changed, not the original.

What I'd like to do is to create a set of uniquely named linked tables
from a set of 3 databases, all verisons of Northwind. So, they all have
the same set of tables, names and all. I just want to have uniquely named
links to them. I can do that manually quite easily.

Thanks for your advice on this. I'll be looking at all the books for
information, too.

You can't append TableDef objects that hat already been appended.
Try something like this:

Dim MyDatabase As Database, dbs As Database
Dim tdfSource As TableDef, tdf As TableDef
Dim strName As String

Set MyDatabase = OpenDatabase("D:\NWNKBE.mdb")
Set dbs = CurrentDb
For Each tdfSource In MyDatabase.TableDefs
strName = tdfSource.Name
If Not strName Like "MSys*" Then
Debug.Print strName
Set tdf = dbs.CreateTableDef("NK" & strName)
tdf.Connect = ";DATABASE=" & MyDatabase.Name
tdf.SourceTableName = strName
dbs.TableDefs.Append tdf
Set tdf = Nothing
End If
Next
MyDatabase.Close
Set MyDatabase = Nothing
Set dbs = Nothing

 

[Tom Ellison]

Thank you! I'll be giving this a try later.

Tom Ellison

 

[PC Datasheet]

Tom,

MyDatabase in MyDatabase.TableDefs(i).Name = refers to D:\NWNKBE.mdb so your
code is renaming the tables in D:\NWNKBE.mdb.

Consider using TransferDataBase to link all the tables in D:\NWNKBE.mdb to
your local database. You can assign the names you want for the linked tables
directly in the TransferDatabase method.

DoCmd.TransferDatabase [transfertype], databasetype, databasename[,
objecttype], source, destination[, structureonly][, saveloginid]

Assign the names you want for the linked tables in the destination
parameter.

 

[StopThisAdvertising]

"PC Datasheet" <[email protected]> schreef in bericht
--

PC Datasheet
Your Resource For Help With Access, Excel And Word Applications 'Resource ????
Over 1175 users have come to me from the newsgroups requesting help '1175 users ????
(e-mail address removed)

--
To Steve:
You still don't get it? No-one wants your advertising/job hunting here!
Over 850 !! users from the newsgroups have visited the website to read what kind of a 'resource' you are... (rapidly increasing..)

To the original poster:
Most people here have a common belief that the newsgroups are for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell his services.
And he is known here as a shameless liar with no ethics at all.

Before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html

Arno R

 

[Tom Ellison]

Dear Wolfgang:

I have a version of what you provided working now.

Tom Ellison

Thank you! I'll be giving this a try later.

Tom Ellison