Query Table Structure

In SQL Server it is possible to query system tables to figure out what the structure of a table is - column names and types.  Is it possible to somehow get a list of column names and types in Access?  And, if so of course, how?
LVL 6
robertjbarkerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

stevbeCommented:
not from system tables, you would need to write code. You can open a schema recordsets that you can use to determine table/column info (among other things). Here is some code that will iterate through all the fields and print them to the debug window so you can pick and chjoose exactly waht fields you need.

Public Function TableSchema()
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field

Set rst = CurrentProject.Connection.OpenSchema(adSchemaTables)
For Each fld In rst.Fields
   If Not IsNull(fld.Value) Then
      Debug.Print fld.Name & vbTab & fld.Value
   Else
      Debug.Print fld.Name & vbTab & "null"
   End If
Next
rst.Close
Set rst = Nothing
End Function

Steve
0
robertjbarkerAuthor Commented:
Stevbe, does your code work with both Access and SQL Server tables?
0
stevbeCommented:
I believe so because but only because it is using standard ADO library which is only translating through to the OLEDB provider and I would imagine that the SQL Server driver exposes schema information and not because I have used this to investigate SQL Server tables. Another route is to explore ADOX object library.

Steve
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

robertjbarkerAuthor Commented:
I created an Access MDB, with table TestTable.  TestTable has columns:

autokey autonumber
textfield text
memofield memo
intfield number

I created a module with a procedure containing the code you supplied and ran immediate.  I got the following in the results, no mention of TestTable!  Know what I'm doing wrong?

TABLE_SCHEMA    null
TABLE_NAME  MSysAccessObjects
TABLE_TYPE  ACCESS TABLE
TABLE_GUID  null
DESCRIPTION null
TABLE_PROPID    null
DATE_CREATED    11/26/2003 10:19:15 AM
DATE_MODIFIED   11/26/2003 10:19:15 AM
TABLE_CATALOG   null
TABLE_SCHEMA    null
TABLE_NAME  MSysAccessObjects
TABLE_TYPE  ACCESS TABLE
TABLE_GUID  null
DESCRIPTION null
TABLE_PROPID    null
DATE_CREATED    11/26/2003 10:19:15 AM
DATE_MODIFIED   11/26/2003 10:19:15 AM
0
stevbeCommented:
ok ... this loops through all tables ...
Public Function TableSchema()
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field

Set rst = CurrentProject.Connection.OpenSchema(adSchemaTables)
Do While Not rst.EOF
    For Each fld In rst.Fields
       If Not IsNull(fld.Value) Then
          Debug.Print fld.Name & vbTab & fld.Value
       Else
          Debug.Print fld.Name & vbTab & "null"
       End If
    Next
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Function


If you change adSchemaTables to adSchemaColumns you should get what you are looking for. If you look up adSchema in the object browser you will see all of the different schema recordsets you can get.

Steve
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
robertjbarkerAuthor Commented:
OK, very good!

So, where did you get to know this?  I find it very difficult to find information like this other than to ask on EE.
EE is very nice, but documentation on dealing with recordsets, for example, ought to be well documented SOMEWHERE.

Any pointers would be greatly appreciated!
0
stevbeCommented:
Access 2002 Desktop Developers Handbook published by Sybex by Litwin, Getz and Gunderloy. Some mix of these authors and Gilbert (another author) have published version from 2.0 through 2002. Statrting with 2000 they split the book into 2, because they cover so much ground, 1 for Desktop and 1 for Enterprise. These books cover advanced topics and loads of code. Usually you can find them bundled with their VBA Developers handbook as a complete 3 book set ... I buy everything from them as soon as it is published.

Steve
0
robertjbarkerAuthor Commented:
Hmm...

I bought the three books.  Maybe I should actually read them.
0
stevbeCommented:
page 278 of the desktop 2002 :-)

I use to read them on long airplane flights going to offsite locations.
0
ismatCommented:
Don't have a enough knowledge of Access database. My expertises are on SQL Server. Need to get information about an access database tables structure. Basically same thing asked by "robertjbarker".
Can I have detailed info about the process or code to do that. Not able to get idea from above answers/explanations that where do I need to create that method/function and what other steps do I need to do to see detailed structure of Access tables.
Just want to get each table name with fields and their data types in a single shot. Would appreciate help.
0
robertjbarkerAuthor Commented:
It really does depend on what you want to do with the information.  In my case I was going to construct sql statements.  So, I needed to get and manipulate the info in code.  If you just want to look at the structure of tables, it's much easier to look from within the table designer.

To be honest, I'm more familiar with SQL Server also.  I did not continue the project I thought I was going to use this in, but if I were, I would do it in SQL Server - or MSDE.
0
stevbeCommented:
ismat, perhaps you should open your own question as Robert's has been asked and answered.

Steve
0
ismatCommented:
stevbe, you are right. I need to open up my question. Basically, I need to build SQL Statement for SQL Server. Don't want to use SQL Server Data migration wizard. With regard to that I am looking to grab table names, fields with datatypes etc.  Thanks.
0
ismatCommented:
Thanks stevbe, for putting me on right track.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.