J
JonWayne
How do I programatically create a table in an external database, that links
a table in the local database?
a table in the local database?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
A
Alex Dybenko
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
currentdb with dbs variable intiated using OpenDatabase with a path to
external DB
J
JonWayne
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
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
D
Douglas J. Steele
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
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
J
JonWayne
Thank you so much. That did it
Douglas J. Steele said: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