Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / March 2005

Tip: Looking for answers? Try searching our database.

Creating Linked Tables (Access 97)

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
JonWayne - 04 Mar 2005 14:07 GMT
How do I programatically create a table in an external database, that links
a table in the local database?
Alex Dybenko - 04 Mar 2005 15:33 GMT
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

Signature

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

> How do I programatically create a table in an external database, that
> links
> a table in the local database?
JonWayne - 04 Mar 2005 16:35 GMT
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

> 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
[quoted text clipped - 8 lines]
> > links
> > a table in the local database?
Douglas J. Steele - 04 Mar 2005 22:41 GMT
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

Signature

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

> I'd like for you to try it and tell me what happens. I do just that and
> when
[quoted text clipped - 41 lines]
>> > links
>> > a table in the local database?
JonWayne - 05 Mar 2005 05:20 GMT
Thank you so much. That did it

> 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.
[quoted text clipped - 67 lines]
> >> > links
> >> > a table in the local database?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.