loop through all flds in all tbls problem

[TrollBro]

I have a db with dozens of tables, each with any number of and types of fields (some numeric, some string, etc). I need to loop through all these tables and populate another table (tblTFVlog) with tblname, fldname, cstrValue, and ValCount for each field in each table except for those fields that are Primary keys, memo fields, or if the field name is/contains "whatever", and also treating nulls as values to count? If I figure out for next loops to cycle through each field in each table, would the best/fastest way then be to use an append sql routine?

i.e. INSERT INTO tblTFVlog (tblname, fldname, Value, Valcount)...SELECT... tbl1.fld1, count(tbl1.fld2) AS countOffld2, .....FROM tblname1
GROUP BY tbl1.fld1;

or is there a better way?

thanks

 

[PHV]

Take a look at the TableDefs, DAO.Fields and DAO.Properties collections.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cmmrfrds]

There are a number of ways to do it. One is looping through the tabledefs as PHV points out. Another is through the ADOX Catalog and also through the ADO openschema method. Here is an example of using the OpenSchema method.


Function TestTableColumn()

Dim cn As New ADODB.Connection, cn2 As New ADODB.Connection
Dim rs As ADODB.Recordset, rs2 As ADODB.Recordset
Dim connString As String
connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\bigtuna\Databases\bank.mdb;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
''cn.Open connString
''cn2.Open connString

Set cn = CurrentProject.Connection
Set cn2 = CurrentProject.Connection

Set rs = cn.OpenSchema(adSchemaTables, _
Array(Empty, Empty, Empty, "table"))

While Not rs.EOF
Debug.Print rs!table_name
Set rs2 = cn2.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, "" & rs!table_name & ""))
While Not rs2.EOF
Debug.Print " " & rs2!column_name
Debug.Print " " & rs2!data_type
Debug.Print " " & rs2!description
Debug.Print " " & rs2!is_nullable
rs2.MoveNext
Wend
rs.MoveNext
Wend
rs.Close
Set cn = Nothing

End Function

 

[TrollBro]

Many Thanks PHV and cmmrfrds - I'll have to digest this a bit - I'm not that familiar with ADODB.Connection - most experience in DAO with A2K. Also having problems with "unable to display db properties" errors that I suspect might cause problems with properties collections.