Solved

Query Table Structure

Posted on 2003-11-26
15
2,184 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
  • 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
 
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 300 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now