By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,460 Members | 2,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,460 IT Pros & Developers. It's quick & easy.

column definitions

P: n/a
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?
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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?

Nov 12 '05 #2

P: n/a
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>
Nov 12 '05 #3

P: n/a
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...
Nov 12 '05 #4

P: n/a
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?

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.