Loop thru fields of database?

[Jaymond Flurrie]

This should be more than easy for any of you using Access VBA a lot.

I would need a program that prints me all the fields in my db. It's similar to Excel's

Sub excelversionprintallsheets()
    Dim wkb As Workbook
    Dim wks As Worksheet
    Set wkb = ThisWorkbook
    
    For Each wks In wkb.Worksheets
        Debug.Print wks.Name
    Next wks
End Sub

so something like

Sub accessversionprintallfields()
    Dim acdb As access.database
    Dim actbl As access.table
    Dim acfld As access.Field
    Set acdb = "c:\accessdatabase.accdb"
    
    For Each actbl In acdb.tables
        For Each acfld In actbl.Fields
            Debug.Print acfld.Name
        Next acfld
    Next actbl
End Sub

but for sure that one above doesn't work. I'm running this from Excel and I have set a reference to Microsoft Access 14.0 Object Library, but I have absolutely no idea what the actual datatypes of those acdb, actbl and acfld should be.

So in other words, please correct the code of Sub accessversionprintallfields to a working one. I really think that it shouldn't be very hard for anyone who has programmed against Access before.

 

[Jaymond Flurrie]

Here's my solution:

Sub accessversionprintallfields()
    Dim acdb As DAO.Database
    Dim actbl As DAO.TableDef
    Dim i As Integer
    Set acdb = DBEngine.OpenDatabase("c:\accessdatabase.accdb")
    
    For Each actbl In acdb.TableDefs
            For i = 0 To actbl.Fields.Count - 1
                Debug.Print actbl.Fields(i).Name
            Next i
    Next actbl
    acdb.Close
End Sub

You need a reference to Microsoft DAO 3.6 Object Library (or similar).

It works, but I have absolutely no idea if there's some smarter way to do this. You have one? Please post it.

 

[SydneyGeek]

It depends how much detail you need. This page has code for documenting tables and indexes. Output is written to another table (created on the fly if it doesn't exist).

Denis

 

[Norie]

There's actually a way to do it with a query but I can't remember it off the top of my head.

I think you would probably need to have a query for each table, or a parameter query based on inputting the table name.

Also, I don't think it's going to give you as much detail as using the code in Denis' link.

 

[SydneyGeek]

Norie, not sure about the table documentor but this does queries:

SELECT mObj.Id
, mObj.Name AS Query
, mQry.Attribute AS Attr
, IIf([mQry].[Attribute]=1,[Name1],Null) AS InsertInto_
, IIf([mQry].[Attribute]<>6 And [mQry].[Attribute]<>1,Nz([Name1],'')) AS From_
, IIf([mQry].[attribute]<>6,[Name2],Null) AS TblQry
, IIf([mQry].[Attribute]=7,Nz([mQry].[Expression],''),'') AS On_
, IIf(mQry.Attribute=6,[Name1],Null) AS Field_Alias
, IIf(mQry.attribute=6,[Name2],IIf((mQry.attribute<>7) And (mQry.attribute<>8) And (mQry.attribute<>11),Replace(Nz(mQry.Expression,''),'=',' = '))) AS Field_
, IIf([mQry].[attribute]=6,Nz([mQry].[Expression],'')) AS UpdateTo_
, IIf([mQry].[Attribute]=8,Nz([mQry].[Expression],'')) AS Where_
, IIf([mQry].[Attribute]=11,Nz([mQry].[Expression],'')) AS GroupBy_
, mQry.Flag
FROM MSysObjects AS mObj INNER JOIN MSysQueries AS mQry ON mObj.Id = mQry.ObjectId
WHERE (((mQry.Name1) Is Not Null) AND ((Left([mObj].[Name],1))<>"~")) OR (((Left([mObj].[Name],1))<>"~") AND ((mQry.Name2) Is Not Null)) OR (((Left([mObj].[Name],1))<>"~") AND ((mQry.Expression) Is Not Null))
ORDER BY mObj.Name, mQry.Attribute;

It's from a post in Utter Access (can't remember the source for now), and it's pretty cool. With a bit of tweaking the concept could be applied to tables too.

Denis

 

[Norie]

Denis

I was sure the code was far simpler and there was something simple you could change to get the fields for queries or tables.

Mind you I'm pretty sure what I'm thinking of only gave a simple list of fields and at most a list of the field type constants.

Anyway, still prefer the code.

 

[boblarson]

Here's a couple of functions I use:

Public Function ConvertFieldType(strType As Variant)
On Error GoTo Err_ConvertFieldType
Dim Pos As Long, strTypeText As String
If IsNull(strType) Then
    ConvertFieldType = Null
    Exit Function
End If
strTypeText = strType
Select Case strType
                Case 1
                    strTypeText = "Yes/No"
                Case 2
                    strTypeText = "Number (Byte)"
                Case 3
                    strTypeText = "Number (Integer)"
                Case 4
                    strTypeText = "Number (Long Integer)"
                Case 5
                    strTypeText = "Currency"
                Case 6
                    strTypeText = "Number (Single)"
                Case 7
                    strTypeText = "Number (Double)"
                Case 8
                    strTypeText = "Date/Time"
                'Case 9
                Case 10
                    strTypeText = "Text"
                Case 11
                    strTypeText = "OLE Object"
                Case 12
                    strTypeText = "Memo"
                 Case Else
            End Select
ConvertFieldType = strTypeText
Exit_ConvertFieldType:
    Exit Function
Err_ConvertFieldType:
    MsgBox Err & ", " & Error$
End Function

Function GetFields(strTable As String) As String
    Dim db As DAO.Database
    Dim fld As DAO.Field
    
    Set db = CurrentDb
    For Each fld In db.TableDefs(strTable).Fields
        Debug.Print Space(2), fld.Name, ConvertFieldType(fld.Size)
    Next
    
End Function

Function GetTableDefs() As String
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        If InStr(1, tdf.Name, "MSys") = 0 Then
        Debug.Print tdf.Name
            GetFields tdf.Name
        End If
    Next
    
End Function

When used in conjunction with all three the GetTableDefs function will return the name of the table, field names and datatype. Not all datatypes are defined in my ConvertFieldType function as I didn't need them all when I was using this, but you can easily figure the rest out.

 

[mg29booka]

Thanks this has proved useful, though changed the call to ConvertFieldType(fld.Size) to ConvertFieldType(fld.Type).