Loop thru fields of database?

Jaymond Flurrie

Well-known Member
Joined
Sep 22, 2008
Messages
919
Office Version
  1. 365
Platform
  1. Windows
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

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

Code:
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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Here's my solution:


Code:
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Norie, not sure about the table documentor but this does queries:

Code:
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
 
Upvote 0
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.:)
 
Upvote 0
Here's a couple of functions I use:
Code:
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

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

Code:
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.
 
Upvote 0
Thanks this has proved useful, though changed the call to ConvertFieldType(fld.Size) to ConvertFieldType(fld.Type).
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top