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

Hello Tom.

Tom Ellison wrote:
> 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

--
Regards,
Wolfgang

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

Tom Ellison


"Wolfgang Kais" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello Tom.
>
> Tom Ellison wrote:
>> 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
>
> --
> Regards,
> Wolfgang
>

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.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1175 users have come to me from the newsgroups requesting help
(E-Mail Removed)




"Tom Ellison" <(E-Mail Removed)> wrote in message
news:uK7NSd$(E-Mail Removed)...
> 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
>
>

"PC Datasheet" <(E-Mail Removed)> schreef in bericht news:yOZ9g.4736$(E-Mail Removed)...

--
> 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 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

Dear Wolfgang:

I have a version of what you provided working now.

Tom Ellison


"Tom Ellison" <(E-Mail Removed)> wrote in message
news:uxsEL$(E-Mail Removed)...
> Thank you! I'll be giving this a try later.
>
> Tom Ellison
>
>
> "Wolfgang Kais" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Hello Tom.
>>
>> Tom Ellison wrote:
>>> 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
>>
>> --
>> Regards,
>> Wolfgang
>>
>
>