Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Query Table Structure

Posted on 2003-11-26
15
Medium Priority
?
2,235 Views
Last Modified: 2008-02-26
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?
0
Comment
Question by:robertjbarker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
15 Comments
 
LVL 39

Expert Comment

by:stevbe
ID: 9826292
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
 
LVL 6

Author Comment

by:robertjbarker
ID: 9826380
Stevbe, does your code work with both Access and SQL Server tables?
0
 
LVL 39

Expert Comment

by:stevbe
ID: 9826420
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 6

Author Comment

by:robertjbarker
ID: 9826606
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
 
LVL 39

Accepted Solution

by:
stevbe earned 1200 total points
ID: 9826677
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
 
LVL 6

Author Comment

by:robertjbarker
ID: 9826746
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
 
LVL 39

Expert Comment

by:stevbe
ID: 9826792
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
 
LVL 6

Author Comment

by:robertjbarker
ID: 9826860
Hmm...

I bought the three books.  Maybe I should actually read them.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 9826925
page 278 of the desktop 2002 :-)

I use to read them on long airplane flights going to offsite locations.
0
 

Expert Comment

by:ismat
ID: 10158796
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
 
LVL 6

Author Comment

by:robertjbarker
ID: 10161752
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
 
LVL 39

Expert Comment

by:stevbe
ID: 10162966
ismat, perhaps you should open your own question as Robert's has been asked and answered.

Steve
0
 

Expert Comment

by:ismat
ID: 10165244
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
 
LVL 39

Expert Comment

by:stevbe
ID: 10167996
0
 

Expert Comment

by:ismat
ID: 10168254
Thanks stevbe, for putting me on right track.
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question