How do I programatically create a table in an external database, that links
a table in the local database?

Just the same way you do for local database, only you have to replace
currentdb with dbs variable intiated using OpenDatabase with a path to
external DB

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com



"JonWayne" <(E-Mail Removed)> wrote in message
news:GWZVd.378$(E-Mail Removed)...
> How do I programatically create a table in an external database, that
> links
> a table in the local database?
>
>

I'd like for you to try it and tell me what happens. I do just that and when
I append it complains that there are no fields in the table and Append
fails. Then I modified the codes by creating as many fields for the new
table as are in the source table. Again the Append method fails - this time
complaining that there are too many fields.

Sub tester2()
Dim db As Database, tbl As TableDef
Const S$ = "C:\Working\Access\Santarosa\Santarosa.mdb"
Const t$ = "Santarosa Zips 5Digit"

Set db = OpenDatabase("C:\Working\Access\All County\Library.mda")
Set tbl = db.CreateTableDef(t)

'This block was inserted after the first error mentioned above
With tbl.Fields
.Append tbl.CreateField("First_Name", dbText)
.Append tbl.CreateField("Last_Name", dbText)
.Append tbl.CreateField("Address_1", dbText)
.Append tbl.CreateField("City", dbText)
.Append tbl.CreateField("State", dbText)
.Append tbl.CreateField("Postal_Code", dbText)
End With

tbl.Connect = "DATABASE=" & S & ";"
tbl.SourceTableName = t
db.TableDefs.Append tbl 'Each time it would fail at this line

End Sub

"Alex Dybenko" <(E-Mail Removed)> wrote in message
news:eNNPO#(E-Mail Removed)...
> Just the same way you do for local database, only you have to replace
> currentdb with dbs variable intiated using OpenDatabase with a path to
> external DB
>
> --
> Alex Dybenko (MVP)
> http://Alex.Dybenko.com
> http://www.PointLtd.com
>
>
>
> "JonWayne" <(E-Mail Removed)> wrote in message
> news:GWZVd.378$(E-Mail Removed)...
> > How do I programatically create a table in an external database, that
> > links
> > a table in the local database?
> >
> >
>
>

If you're trying to create a table that's linked to a table in another
database, you don't define fields: they're defined by the original table.
However, you need a semi-colon in front of the word Database in the connect
string (and you don't need one at the end)

See whether this works:

Sub tester2()
Dim db As Database, tbl As TableDef
Const S$ = "C:\Working\Access\Santarosa\Santarosa.mdb"
Const t$ = "Santarosa Zips 5Digit"

Set db = OpenDatabase("C:\Working\Access\All County\Library.mda")
Set tbl = db.CreateTableDef(t)

tbl.Connect = ";DATABASE=" & S
tbl.SourceTableName = t
db.TableDefs.Append tbl

End Sub


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



"JonWayne" <(E-Mail Removed)> wrote in message
news:S10Wd.2290$(E-Mail Removed)...
> I'd like for you to try it and tell me what happens. I do just that and
> when
> I append it complains that there are no fields in the table and Append
> fails. Then I modified the codes by creating as many fields for the new
> table as are in the source table. Again the Append method fails - this
> time
> complaining that there are too many fields.
>
> Sub tester2()
> Dim db As Database, tbl As TableDef
> Const S$ = "C:\Working\Access\Santarosa\Santarosa.mdb"
> Const t$ = "Santarosa Zips 5Digit"
>
> Set db = OpenDatabase("C:\Working\Access\All County\Library.mda")
> Set tbl = db.CreateTableDef(t)
>
> 'This block was inserted after the first error mentioned above
> With tbl.Fields
> .Append tbl.CreateField("First_Name", dbText)
> .Append tbl.CreateField("Last_Name", dbText)
> .Append tbl.CreateField("Address_1", dbText)
> .Append tbl.CreateField("City", dbText)
> .Append tbl.CreateField("State", dbText)
> .Append tbl.CreateField("Postal_Code", dbText)
> End With
>
> tbl.Connect = "DATABASE=" & S & ";"
> tbl.SourceTableName = t
> db.TableDefs.Append tbl 'Each time it would fail at this
> line
>
> End Sub
>
> "Alex Dybenko" <(E-Mail Removed)> wrote in message
> news:eNNPO#(E-Mail Removed)...
>> Just the same way you do for local database, only you have to replace
>> currentdb with dbs variable intiated using OpenDatabase with a path to
>> external DB
>>
>> --
>> Alex Dybenko (MVP)
>> http://Alex.Dybenko.com
>> http://www.PointLtd.com
>>
>>
>>
>> "JonWayne" <(E-Mail Removed)> wrote in message
>> news:GWZVd.378$(E-Mail Removed)...
>> > How do I programatically create a table in an external database, that
>> > links
>> > a table in the local database?
>> >
>> >
>>
>>
>
>

Thank you so much. That did it


"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:(E-Mail Removed)...
> If you're trying to create a table that's linked to a table in another
> database, you don't define fields: they're defined by the original table.
> However, you need a semi-colon in front of the word Database in the
connect
> string (and you don't need one at the end)
>
> See whether this works:
>
> Sub tester2()
> Dim db As Database, tbl As TableDef
> Const S$ = "C:\Working\Access\Santarosa\Santarosa.mdb"
> Const t$ = "Santarosa Zips 5Digit"
>
> Set db = OpenDatabase("C:\Working\Access\All County\Library.mda")
> Set tbl = db.CreateTableDef(t)
>
> tbl.Connect = ";DATABASE=" & S
> tbl.SourceTableName = t
> db.TableDefs.Append tbl
>
> End Sub
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>
> "JonWayne" <(E-Mail Removed)> wrote in message
> news:S10Wd.2290$(E-Mail Removed)...
> > I'd like for you to try it and tell me what happens. I do just that and
> > when
> > I append it complains that there are no fields in the table and Append
> > fails. Then I modified the codes by creating as many fields for the new
> > table as are in the source table. Again the Append method fails - this
> > time
> > complaining that there are too many fields.
> >
> > Sub tester2()
> > Dim db As Database, tbl As TableDef
> > Const S$ = "C:\Working\Access\Santarosa\Santarosa.mdb"
> > Const t$ = "Santarosa Zips 5Digit"
> >
> > Set db = OpenDatabase("C:\Working\Access\All County\Library.mda")
> > Set tbl = db.CreateTableDef(t)
> >
> > 'This block was inserted after the first error mentioned above
> > With tbl.Fields
> > .Append tbl.CreateField("First_Name", dbText)
> > .Append tbl.CreateField("Last_Name", dbText)
> > .Append tbl.CreateField("Address_1", dbText)
> > .Append tbl.CreateField("City", dbText)
> > .Append tbl.CreateField("State", dbText)
> > .Append tbl.CreateField("Postal_Code", dbText)
> > End With
> >
> > tbl.Connect = "DATABASE=" & S & ";"
> > tbl.SourceTableName = t
> > db.TableDefs.Append tbl 'Each time it would fail at this
> > line
> >
> > End Sub
> >
> > "Alex Dybenko" <(E-Mail Removed)> wrote in message
> > news:eNNPO#(E-Mail Removed)...
> >> Just the same way you do for local database, only you have to replace
> >> currentdb with dbs variable intiated using OpenDatabase with a path to
> >> external DB
> >>
> >> --
> >> Alex Dybenko (MVP)
> >> http://Alex.Dybenko.com
> >> http://www.PointLtd.com
> >>
> >>
> >>
> >> "JonWayne" <(E-Mail Removed)> wrote in message
> >> news:GWZVd.378$(E-Mail Removed)...
> >> > How do I programatically create a table in an external database, that
> >> > links
> >> > a table in the local database?
> >> >
> >> >
> >>
> >>
> >
> >
>
>