TSQL, reading columns / table structure in access

logic

On an MS SQL Server I can read column information

SELECT 
   ORDINAL_POSITION
  ,COLUMN_NAME
  ,DATA_TYPE
  ,CHARACTER_MAXIMUM_LENGTH
  ,IS_NULLABLE
  ,COLUMN_DEFAULT
FROM   
  INFORMATION_SCHEMA.COLUMNS 
WHERE   
  TABLE_NAME = 'MyDataTable' 

Is it possible to do something similar for MS Access ?

Seth Brundle

Here is something I put together...

Option Compare Database
Option Explicit

Private Sub cmdListFieldProperties_Click()
    Call listTableFields("Prod_cust1")
End Sub

Function listTableFields(strTblName As String) As String
On Error GoTo listTableFields_Error
    Dim db As DAO.Database
    Dim tdfld As DAO.TableDef
    Dim fld As Field
 
    Set db = CurrentDb()
    Set tdfld = db.TableDefs(strTblName)
    Debug.Print "Ordinal Position" & vbTab & "|" & vbTab & _
        "Name" & vbTab & "|" & vbTab & _
        "Type" & vbTab & "|" & vbTab & _
        "Nullable" & vbTab & "|" & vbTab & _
        "Default Value" & vbTab & "|" & vbTab & _
        "Field Size" & vbTab & "|" & vbTab & _
        "Autonumber"
        
    For Each fld In tdfld.Fields
        Debug.Print fld.OrdinalPosition & vbTab & "|" & vbTab & _
            fld.Name & vbTab & "|" & vbTab & _
            FieldType(fld.Type) & vbTab & "|" & vbTab & _
            fld.AllowZeroLength & vbTab & "|" & vbTab & _
            IIf(fld.DefaultValue = "", "None", fld.DefaultValue) & vbTab & "|" & vbTab & _
            fld.Properties("Size") & vbTab & "|" & vbTab & _
            IIf(IsAutoNumber(fld) = True, "Yes", "")
    Next
 
    Set tdfld = Nothing
    Set db = Nothing
If Err.Number = 0 Then Exit Function

listTableFields_Error:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: listTableFields" & vbCrLf & _
    "Error Description: " & Err.Description, vbCritical, "An Error has Occured!"
    Exit Function
End Function

Function IsAutoNumber(ByRef fld As DAO.Field) As Boolean
On Error GoTo ErrHandler

  IsAutoNumber = (fld.Attributes And dbAutoIncrField)

ExitHere:
  Exit Function
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Function

Public Function FieldType(IntegerType As Integer) As String

  Select Case IntegerType
  
    Case dbText
      FieldType = "Text"
    Case dbMemo
      FieldType = "Memo / Hyperlink"
    Case dbDate
      FieldType = "Date / Time"
    Case dbCurrency
      FieldType = "Currency"
    Case dbBoolean
      FieldType = "Boolean"
    Case dbInteger
        FieldType = "Integer"
    Case dbSingle
        FieldType = "Single"
    Case dbDouble
        FieldType = "Double"
    Case dbByte
        FieldType = "Byte"
    Case dbLong
        FieldType = "Long Integer / Autonumber"
    Case dbBinary
        FieldType = "Binary"
    Case dbDouble
        FieldType = "Double"
    Case dbLongBinary
        FieldType = "OLE Object"
    Case Else
        FieldType = "Other"
  End Select
End Function

It outputs to the immediate window (Ctrl & G). Butcher it up if you want to use it within an SQL statement.

Seth Brundle

Looks to be somewhat simpler in TSQL!
:rolleyes:

logic

Thanks for that. Is that vb6 or vbscript. I am trying to do this in vb2008. I can't find DAO in the object browser, is that not outdated now?

Seth Brundle

This is VBA code run from MS Access.

1. add a command button to a blank form. Name it cmdListFieldProperties.
2. Right click on your button and choose Build Event and from the dialog, choose Code Builder.
3. The MS Visual Basic Editor (VBA Window) should open.
4. Assuming there is no other code there, remove all code from that "page" and then copy & paste my code into it.
5. Then from the menubar, choose Debut > Compile. Then Save and test your button (press Ctrl & G in the VBA Editor to open the immediate window before you run as this is currently where your outupt is shown)

To use this in Visual Studio 2008, you will need to change it so that the information can be sent over to VS.