column definitions

[Isaac Dealey]

I've been searching for hours and hours and can't find what I need.

I'm creating a scripted web application that's designed to manage
multiple databases (MS SQL Server, MS Access, Oracle, PostgreSQL,
MySQL) using the same interface. I need to figure out how to create a
recordset that contains a list of all the column properties (column
name, data type, character length) for a specified table. The
MSysObjects table doesn't seem to contain any column information, and
I've seen several places where MS MVP's have said they don't think
Access exposes this information in a table like SQL Server
(syscolumns, information_schema.columns) or Oracle (all_tab_columns,
col). If there's a way to create a query, macro, etc. in Access that
will produce a recordset and be accessible by sql or there's a way to
access the information via COM either way will likely be enough for me
to do what I need, but I can't seem to find any clearly described or
succinct information about it.

Thanks,

Isaac

p.s. What is Type 3 in the MSysObjects table?

[Phil Stanton]

Think Type 3 are subforms
"Isaac Dealey" <in**@turnkey.to> wrote in message
news:ad**************************@posting.google.c om...

I've been searching for hours and hours and can't find what I need.

I'm creating a scripted web application that's designed to manage
multiple databases (MS SQL Server, MS Access, Oracle, PostgreSQL,
MySQL) using the same interface. I need to figure out how to create a
recordset that contains a list of all the column properties (column
name, data type, character length) for a specified table. The
MSysObjects table doesn't seem to contain any column information, and
I've seen several places where MS MVP's have said they don't think
Access exposes this information in a table like SQL Server
(syscolumns, information_schema.columns) or Oracle (all_tab_columns,
col). If there's a way to create a query, macro, etc. in Access that
will produce a recordset and be accessible by sql or there's a way to
access the information via COM either way will likely be enough for me
to do what I need, but I can't seem to find any clearly described or
succinct information about it.

Thanks,

Isaac

p.s. What is Type 3 in the MSysObjects table?

[rkc]

"Isaac Dealey" <in**@turnkey.to> wrote in message
news:ad**************************@posting.google.c om...

I've been searching for hours and hours and can't find what I need.

I'm creating a scripted web application that's designed to manage
multiple databases (MS SQL Server, MS Access, Oracle, PostgreSQL,
MySQL) using the same interface. I need to figure out how to create a
recordset that contains a list of all the column properties (column
name, data type, character length) for a specified table.

The OpenShema method of the ADODB Connection object is most
likely what you are looking for.

Here's a short example I tested in the Northwind example database.
Obviously you'll have to do some more reading up on everything that's
involved with OpenSchema, but it should be a shove in the right
direction.

<Example Code>

Sub ShowColumnProperties(TableName As String)
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field

Set rs = CurrentProject.Connection.OpenSchema _
(adSchemaColumns, Array(Empty, Empty, TableName))

' For Each fld In rs.Fields
' Debug.Print fld.Name
' Next

With rs
Do While Not .EOF
Debug.Print !COLUMN_NAME
Debug.Print " "; !DATA_TYPE
Debug.Print " "; !CHARACTER_MAXIMUM_LENGTH
Debug.Print " "; !Description
.MoveNext
Loop
End With

rs.Close
Set rs = Nothing
End Sub

</Example Code>

[Pieter Linden]

I think if you use ADO, you can query the DB schema and get all the
details you need - tablenames, field names, types, sizes, properties
and all that...

[TC]

Jet exposes table schemas via DAO objects. You could use those objects to
create a table with the relevant information, then open a recordset on that
table. Roundabout, but ...!

Something like this (untested):

dim db as database, td as tabledef, fld as field
set db=currentdb()
for each td in db.tabledefs
for each fld in td.fields
debug.print td.name, fld.name, fld.type
next
next
set db=nothing

Of course, that code just prints the details to the debug window. But you
could easily go from there, to saving them into a database table.

HTH,
TC
"Isaac Dealey" <in**@turnkey.to> wrote in message
news:ad**************************@posting.google.c om...

I've been searching for hours and hours and can't find what I need.

I'm creating a scripted web application that's designed to manage
multiple databases (MS SQL Server, MS Access, Oracle, PostgreSQL,
MySQL) using the same interface. I need to figure out how to create a
recordset that contains a list of all the column properties (column
name, data type, character length) for a specified table. The
MSysObjects table doesn't seem to contain any column information, and
I've seen several places where MS MVP's have said they don't think
Access exposes this information in a table like SQL Server
(syscolumns, information_schema.columns) or Oracle (all_tab_columns,
col). If there's a way to create a query, macro, etc. in Access that
will produce a recordset and be accessible by sql or there's a way to
access the information via COM either way will likely be enough for me
to do what I need, but I can't seem to find any clearly described or
succinct information about it.

Thanks,

Isaac

p.s. What is Type 3 in the MSysObjects table?