Solved

Query Table Structure

Posted on 2003-11-26
15
2,212 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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
 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

807 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