Accessing Tables & Fields from an External DB

[cmp077]

I want to extract all the tables and underlying fields from an access databases into another table within a seperate access database.

For example, lets say I have two databases Master and Vendor. In Vendor.mdb there are 10 tables with 10 fields each.

From the Master.mdb I want to run a query that will go into the Vendor.mdb file and extract/copy the 10 tables with there fields and paste them into a table called tblMaster inside the Master.mdb database. Is there a way to do this?

Let me know. Thanks!

 

[PHV]

Why not simply play with linked tables ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[grande]

File --> Get External Data --> Link Tables...

This will bring in all the record from Vendor.mdb, and they will act as if they are in your database.

Let me know if you need help inserting the info into tblMaster.

-------------------------
Just call me Captain Awesome.

 

[cmp077]

The linked table feature or import feature will not work in this case. I do not want a collection of individual tables from various DB's in my "Master Database"

To be more clear what I essentialy want is to have 2 tables (tblMasterTable & tblMasterTableFields.)

Each record in the tblMasterTable would be a table name that had been extracted from the external Access DB. Each record in the tblMasterTableFields would then contain all the fields from the corresponding record (the extracted table) in the tblMasterTable.

For example if I have a Vendor.mdb file and in that DB I have table VendorName with 5 fields and VendorAddress with 5 fields. What I want to do is to extract the VendorName & VendorAddress table names into the tblMasterTable in the Master.mdb database so that they would appear as two seperate records.

Then in the tblMasterTableFields table you would see two records that list the field names for each of those tables in the tblMasterTable.

Is this clear?

 

[PHV]

And you don't know how to make Append/Update queries based on linked tables ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

PHV, he doesn't want the values, he wants the definition information.

tblmasterTable:

TableID      TableName
  1          tblVendor
  2          tblVendorAddress


tblMasterTableDetail:

DetailID      TableID        FieldName
  1              1           VendorID
  2              1           VendorName
  3              1           ContactName
  4              2           MailingAddress
  5              2           StreetAddress

Leslie

 

[cmp077]

You don't seem to be understanding the essence of my question so let me see if I can explain it better.

If I have a Vendor DB with a table called tblVendor that consists of the following fields:VendorID, VendorName & VendorState.

Now imagine I have another database called Master.mdb. This database has two tables called tblMasterTableNames and tblMasterFieldNames.

From this database I want to go into the Vendor DB and retrieve the the table name and the field names from the tblVendor table.

I do not want the actual data that is in the tblVendor table so the linked table feature is not the way to go.

The table names should appear as a record in the tblMasterTableNames with the fields that make up that table appearing as a record in the tblMasterFieldNames.

 

[cmp077]

lespaul -

thanks for responding to me. how would I code this though to actually make it work. I am not sure.

thanks again.

 

[lespaul]

I don't have a clue how to get the data, I was just trying to help PHV "see" what you wanted in a picture, rather than words (seeing a picture of the expected results does wonders for getting great answers!)

As far as accomplishing this, I think it depends on the external database you are using. There are special system tables for most RDMS, but I don't know any of them.



Leslie

 

[cmp077]

If anyone know's of a way to dynamically gain access to just the table names and field names of an external access database from within a seperate access db then please let me know.

This is an example of what I am trying to do.

Say I have a Vendor DB with a table called tblVendor that consists of the following fields:VendorID, VendorName & VendorState.

Now imagine I have another database called Master.mdb. This database has two tables called tblMasterTableNames and tblMasterFieldNames.

From this database I want to go into the Vendor DB and retrieve the the table name and the field names from the tblVendor table.

I do not want the actual data that is in the tblVendor table so the linked table feature is not the way to go.

The table names should appear as a record in the tblMasterTableNames with the fields that make up that table appearing as a record in the tblMasterFieldNames. See the comments posted by lespaul for a visual description of what I am trying to do.

Thanks for your help.

 

[PHV]

Take a look at the DAO.Database, DAO.TableDef(s) and DAO.Field(s) objects(collections).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cmp077]

Sorry I have never heard of that. Where do I find this?

 

[PHV]

When in VBE (Alt+F11) open the Object browser window (F2) and feel free to play with the F1 key.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[traingamer]

I think I'd import the tables into your master database with the 'Definition Only' option and work with them in the master database using the PH's suggestion.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[cmp077]

Guys thanks for your responses, however could you please be more specific and elaborate when responding.

While your responses are helpful unfortunately they have not allowed me to import only the table names and corresponding field names as individual records. If you know how to do this dynamically then please let me know.

Thanks again for your help, I appreciate it!

 

[lespaul]

After PHV's response I searched these fora for "TableDefs", here are two of many threads that describe how to at least get the table names.....

Thread705-905942
Thread700-900684

HTH