Access VBA Query Syntax to List Field Names of Table

I am trying to create a VBA procedure in Access 2007 that will populate a listbox with field names from a table. The list box would have 2 columns - field name and caption. Can someone point me in the right direction for the query syntax used to select the field names from a table using ADO? Thanks!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You can use ADOX.

You will have to add references to  the Microsoft ActiveX Data Objects Library as well as the Microsoft ADO Ext For Dll and Security.

This example prints the info in debug. You can alter it to insert the items to a table or recordset.

Sub GetTableNamesWithFieldsUsingADOX()
  Dim Conn As New ADODB.Connection

  'Open connection you want To get database objects
  Set Conn = CurrentProject.Connection

  'Create catalog object
  Dim Catalog As New ADOX.Catalog
  Set Catalog.ActiveConnection = Conn

  'List tables And columns
  Dim Table As ADOX.Table, Column As ADOX.Column
  For Each Table In Catalog.Tables
    For Each Column In Table.Columns
      Debug.Print Table.Name & ", " & Column.Name
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

What is "caption"?
Tony HungateTechnical Writer/Trainer | CISSPCommented:
Try  setting the RowSourceType of the listbox property to Field List and the RowSource to the table name.

Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

That's an interesting question!
If the field has no caption then the routine throws error 3270.
So, that error is caught

This code assume DAO 3.6
Disambiguate if you have ADO on the go.
Call the function and pass in the desired table name from somewhere on the form in question.
The listbox needs to be set to Value List, and 2 columns

Again, that was a neat question!
Have a gander here, too
Public Function BuildValueList(TableName As String)
On Error GoTo myerror
Dim FinalString As String
Dim db As Database
Dim rs As Recordset
Dim myfield As Field
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from " & TableName & " where 1 = 2;", dbOpenDynaset, dbSeeChanges)

For Each myfield In rs.Fields
    FinalString = FinalString & myfield.Name & ";" & Nz(myfield.Properties("Caption"), "no caption") & ";"
Next myfield

Me.List0.RowSource = FinalString  '<--------------------replace List0 with your listbox name

Exit Function

If Err.Number = 3270 Then 'no existing caption
    FinalString = FinalString & myfield.Name & ";" & "no caption" & ";"
    Resume Next
End If

End Function

Open in new window

Jeffrey CoachmanMIS LiasonCommented:
You can also just use a combobox.

Set the RowSoureType property to: Field List
Set the RowSource property to the name of the table

Click the dropdown on the combobox, and a list of the fields in the table are listed.
He wants the field captions as a column in the listbox. I don't think a field list will do that
Jeffrey CoachmanMIS LiasonCommented:

OK, I just read this as the question title...
It's a neat question.
Until I poked at it, I didn't realize that the labels of bound controls would pick up the field caption.
All of those gazillions of labels whose caption I changed from MyFieldName to My Field Name that could have been done for me if I had just added a caption to the field.
All of the combo boxes bound to ID fields that I could have had a caption for and not needed to deep-six the ID from the label.


You learn something new every day :)
Jeffrey CoachmanMIS LiasonCommented:
<You learn something new every day :)>
   "Everyday is a school day"

The catch is that sometimes I change the Label caption and forget to make the corresponding change to the Table field caption property.

BTW, on a side note...
Just curious...
Is there a reason why you don't contribute more as an Expert here?


Time constraints.
Expertise constraints.

Politeness.  If someone else is working with an author and going in the right direction, there's no reason to chime in.
You, @DatabaseMx, @JDettman, and @LSMConsulting seem to have the same skill set I do, and do an excellent job of answering questions :)

If I see something Access-related languishing, or not going the right direction, I comment.


Even if other experts are headed in the right direction, you might offer a slightly different code structure or performance boost that would add to the quality of the solution.  Sometimes, it is nice to have another expert support my approach.
Jeffrey CoachmanMIS LiasonCommented:

You have several solution on the table....
By the right direction, I mean solved in the way I think would be elegant, most efficient, compact, well-documented and beneficial to the author and others who might google it afterwards.
jandrews404Author Commented:
Sorry for the delayed response. That was exactly what I was looking for.....thanks!!
I'm not sure how your accepted answer gave you the field captions, never mind put them in a listbox -- but it did use ADO.
If you did alter the solution's code to get what you needed, can you post it?
There's a DAO solution here, but no ADO one.
It'd be good to have them both
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.