Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2243
  • Last Modified:

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?
0
robertjbarker
Asked:
robertjbarker
  • 7
  • 5
  • 3
1 Solution
 
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now