Unlike most dbms's Access doesn't have the system tables that allow you to
retrieve table and field info though SQL statements; to my knowledge (I
avoid Access like the plague so there may be easier ways than what follows).
Under win you can the ADODB.Connection class object and retrieve them this
way (where dsn, username, pass are vars):
<cfscript>
// initalise the DB object and open a connection to the desired db
dbo = createObject("COM", "ADODB.Connection") ;
dbo.Open("DSN=" & dsn, username, password, -1) ;
// get schema (schema 4 is field info, 20 table info) recordset
recordset = dbo.OpenSchema(4) ;
// assign fields to "Fields" collection from returned schema
fields = recordset.Fields ;
...
The fields collection contains all the info you'll need, and can be accessed
like this:
table_name = fields.Item("TABLE_NAME") ;
// display table current field belongs too
writeOutput(table_name.value) ;
Other valid fields in the schema recordset are; COLUMN_NAME, DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_FLAGS, etc.
// move the cursor to the next record
recordset.MoveNext() ;
In the way above you can loop through all the data in the schema, to keep
track of it all I'd suggest using a struct. This may lots more info than you
need, but it's the only way I know to get datatypes and other design info
from an Access table. Much, much, more difficult than simply querying the db
as in other dbms's.
-----Original Message-----
From: Terry Troxel [mailto:[EMAIL PROTECTED]]
Sent: January 17, 2001 13:33
To: CF-Talk
Subject: retreiving datatypes
I know that I can build a list of column names in an Accrss table with the
columnlist variable, but is there also a way I can do the same thing in
order to retrieve the corresponding datatypes for all the fields in an
Access table?
Terry Troxel
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
