Recording Field Definition Data

[sha76]

Hi,

Any help on the following would be greatly appreciated as I really don't have a clue where to start!

All our data is currently stored on an Informix server. I need to create an Access table showing the data type & field size for each field in every table. I use ODBC to link to the tables.

Can I access these properties with vba? (couldn't find anything useful in help, but not sure I was searching for the right thing)

Thanks for you help

Sharon

 

[MichaelRed]

GENERALLY, the information is available for 'foregin' databases through the db vendor. It is often provided in the form of sample code in the vendor's doeumentation / tools Ms. A. Help / tools doesn't include this. Dependig on the version of the db, and what access rights you have to the db, you should be able to programmatically retrieve the database schema through any of several approaches. In the simplest case, you shoould be able to simply query the db for the schema and return a recordset with the pertinient info.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[sha76]

I can see all the info in design view of a linked table, so I was hoping I could maybe access these properties through vba? Is this possible or are there any other /better ways?

Cheers

Sharon

 

[MichaelRed]

I've never tried it this way. Native tables' properties are accessed through the Containers objects, with some level of drill down. There are NUMEROUS threads here (e/g/ Tek-Tips Ms A. fora (the several of them), some with at least partial sample code. You basically open the Container of the tables, then itterate through the collection. Within the individual object (table), itterate through the fields collection, obtaining the desired properties (name, type, length ... ) and depositing them into some object for further use.

I rember having posted something on this topic here (Tek-Tips) recently, but cannot locate it at the moment. As far as I recall, it is just really an adaptation of the example(s) in Ms. A. Help.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[sha76]

Cheers Michael,

I shall have a look next year!

Happy Christmas!

Sharon

 

[cmmrfrds]

You can get the data types etc.. from the query definition as MichaelRed suggested. One thing to be aware of is that there is usually some compromise by the ODBC driver when mapping to Access data types. The major database vendors typically have more data types than Access. For example, the driver will probably map various character types in Informix to the Access text type. If Informix data types are required then using the query definition may not work for you.

Another way may be to generate the data types into a list on the Informix side and then read the list as a text file in Access to build the Access table.