enumerating tables collection or fields in ADOX blows up Access

[Mark VII]

Greetings --

The quick question here is "can I get a list of fields in a table without
enumerating the fields collection of the tabledef"? Trying to do this is
blowing up Access.

Here's the long version of the question -- I'm working with a rather complex
database where I have a combo box on a form, populated with name of the
tables in the database. Another combo box lists the fields in the selected
table. The table list combo box's row source is a custom function that
enumerates the tables collection of the database. The field list combo box's
row source is a custom function that enumerates the fields collection of the
selected table.

Everything had been working, then suddenly, any of the code that steps
through either the tables collection or the fields collection of a table
causes Access to "experience an error and need to shut down".

Even this little snippet blows up at the "For Each tdf In cat.Tables"
statement.

Public Function TableLister()

Dim dbs As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tdf As ADOX.Table

Set dbs = CurrentProject.Connection
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = dbs

For Each tdf In cat.Tables
Debug.Print tdf.Name
Next tdf

End Function

I've tried decompiling the database followed by compact and repair, no luck.
I've created a new database and imported the objects from the existing
database, no luck. This logic "used to work", and the modfications I made
most recently don't even touch this code.

Does anyone have any suggestions? I was able to stop enumerating the tables
collection by querying MsysObjects, but I haven't found a way to list the
fields in a table without enumerating the fields collection.

Thanks....
Mark

 

[Allen Browne]

What version of the ADOX library are you referencing?

To be honest, I haven't used ADOX extensively, because (like you) I found it
to be unstable and prone to versioning issues.

DAO is the native Access library - much more stable and consistent across
versions. The undocumented MSysObjects would be my preferred approach: it
works in all versions of Access. If you really don't want to do that, recent
versions expose the AllTables collection for you to loop through.

Once you get to the name of the table you want, load its field names into a
combo or list box just by setting its properties like this:
Row Source Type field list
Row Source Table1

Alternatively, you can loop the Fields of the TableDef to get the field
names and other properties, as in this example:
http://allenbrowne.com/func-06.html

Or, ADO offers an OpenSchema if you want to avoid DAO:
http://allenbrowne.com/func-ADO.html#ShowSchema

 

[Mark VII]

Hi Allen --

I was trying to go with your suggestion to get the field list via property
settings, and couldn't get that to work. In my situation, I can't set a
"hard" value for the table name, because this form is mapping source
table/field combinations to target table/field combinations. The field name
list always comes up empty, even if I requery the combo box. This is what I
tried for the row source property:

-- forms(<form name>).<table combo box name> (with and without quotes around
the form name
-- form!<form name>!<table combo box name> (with and without square brackets)
-- <table combo box name>

Also, I'm using ADOX 2.8 for DDL and Security.

Thanks a million,
Mark

 

[Allen Browne]

Mark, all you need is a form with 2 unbound combos:
- cboTable for selecting a table, and
- cboField, for selecting a field from that table.

cboField has its Row Source Type set to Field List, and leave its Row Source
blank.

cboTable has RowSource of:
SELECT MSysObjects.Name
FROM MSysObjects
WHERE (MSysObjects.Type) In (1,4,6)) AND NOT
(([MSysObjects].[Name] Like "~*")
Or ([MSysObjects].[Name] Like "MSys*"))
ORDER BY MSysObjects.Name;

Then set up the After Update of cboTable to this event procedure:

Private Sub cboTable_AfterUpdate()
With Me.cboField
.Value = Null
.RowSource = Nz(Me.cboTable, vbNullString)
End With
End Sub

 

[Mark VII]

Hi Allen --

Your suggestion worked beautifully, and just saved me having to convert the
whole doggone app back to DAO.

Thanks a milion,
Mark