How can I Link table data to "Data Dictionary" data

[mlooman]

I want to replace an iterative loop by using a query of columns from a "Data Dictionary" table, linked to a regular data table to insert a number of values at once into a history table, rather than run through each field in the recordset, compare it to its previous value and insert it individually into the history table.

The problem, is I can't find a system table like SQL Server's "syscolumns" or information_schema.columns tables to use. The only alternative, I see is to build my own columns table, populate it from the .TableDefs.(x).Fields collections and maintain it as tables change. I would really prefer not to have to implement it this way.

Please let me know if there is a way to query the column names of a table, rather than iterate through them from the Fields collection.

The reason for the change is to improve the performance of my system. It seems to me one larger SQL insert statement should run much faster than iterating through several smaller ones.

 

[boblarson]

Please let me know if there is a way to query the column names of a table, rather than iterate through them from the Fields collection.

Nope, you get the joy of iterating, sorry.

The reason for the change is to improve the performance of my system. It seems to me one larger SQL insert statement should run much faster than iterating through several smaller ones.

Makes complete sense but our choices here are sadly lacking.

 

[Uncle Gizmo]

You may find THIS THREAD helpful...

 

[boblarson]

You may find THIS THREAD helpful...

That thread still shows iterating, not getting them with a SQL Statement.

 

[Uncle Gizmo]

Go to this link: http://msaccesshintsandtips.ning.com/profiles/blog/show?id=948619:BlogPost:7031

And download the example at:::

Download the database used in the following videos here: (This is the "empty" database before the following modifications are carried out)

The List Generated is shoved into the combo box as a record source, I wonder if there’s a way of shoving it into a query or a temporary table?

 

[Uncle Gizmo]

I wonder, I reckon you could shove them into an array and pick them out of that couldn’t you?

 

[boblarson]

I wonder, I reckon you could shove them into an array and pick them out of that couldn’t you?

Tony:

It is a point you can't get around. You have to iterate through the tables and fields collections in order to get the field names. There is no SQL statement you can run (at least that I've ever seen) that can pull them in one shot. That is what the OP wants because iterating through the tables and fields collections is slow. And no matter how you slice it, you have to iterate to get them, whether it is to use them in a combo box row source or to assign them to an array. It is still very slow.

 

[Uncle Gizmo]

I found this thread, looks very interesting, could generate the table for you I recon.
http://www.access-programmers.co.uk/forums/showthread.php?t=33855

 

[boblarson]

I found this thread, looks very interesting, could generate the table for you I recon.
http://www.access-programmers.co.uk/forums/showthread.php?t=33855

And that is what the OP referred to when they said

The only alternative, I see is to build my own columns table, populate it from the .TableDefs.(x).Fields collections and maintain it as tables change. I would really prefer not to have to implement it this way.

To which it still would require an iteration through the tables/fields collections for creating the table and then periodically for maintaining the table.

 

[DCrake]

Ok

I simply added a combo box to a form and set the row source type to FieldList
and then selected a table form the tables collection.

Then on the on load of the form

Private Sub Form_Load()
For x = 0 To Me.Combo0.ListCount - 1
    Debug.Print Me.Combo0.ItemData(x)
Next

End Sub

This then listed all the fields in the immediate window. You could instead sabve them to an array, thus giving you a portable list of fields form a specified table.

David

 

[mlooman]

All, Thank you for your feedback. Given the limitations, I've chosen to implement my own columns table, and use that to "unpivot" the data tables when needed. Here is the code i am using to create/update the columns table and the query to "Unpivot" a table's data using the columns data dictionary table.

Maintaining the columns "Data Dictionary" table.

Public Sub RebuildDataDictionary()
'This code was originally written by Matthew Looman
'You are free to use it in any application, provided
'original attribution is included.
' Matthew Looman 2009
  Dim fld As Field
  Dim idx As index
  Dim rcd As Recordset
  Dim tdf As TableDef
 
  On Error GoTo does_not_exist
  DoCmd.DeleteObject acTable, "dd_columns"          'Remove the Table if it exists.  This completely rebuilds the table.
  On Error GoTo RebuildDataDictionary_Error
 
  Set tdf = CurrentDb.CreateTableDef("dd_columns", dbHiddenObject)
  With tdf
    'Create and append fields
    'Possible extensions could include position_ordinals, or data types.
    .Fields.Append .CreateField("table_name", dbText, 255)
    .Fields.Append .CreateField("column_name", dbText, 255)
 
    'Create Indexes for the Table.
    'Indexes included here to improve performance in usage for "Unpivoting"
    Set idx_1 = .CreateIndex("table_name_index")
    With idx_1
      .Fields.Append .CreateField("table_name")
    End With
 
    Set idx_2 = .CreateIndex("dd_columns_primary_key")
    With idx_2
      .Fields.Append .CreateField("table_name")
      .Fields.Append .CreateField("column_name")
    End With
 
    .Indexes.Append idx_2
    .Indexes.Append idx_1
  End With
  CurrentDb.TableDefs.Append tdf  'Append the table to the database's tables collection.
 
  'Iterate through the set of Tables and columns and to populate the new dd_columns Table.
  Set rcd = CurrentDb.OpenRecordset("dd_columns")
  With rcd
  For Each tbl In CurrentDb.TableDefs
    For Each fld In tbl.Fields
      .AddNew
      !table_name = tbl.name
      !column_name = fld.name
      .Update
    Next fld
  Next tbl
  End With
 
RebuildDataDictionary_Exit:
  Exit Sub
 
RebuildDataDictionary_Error:
  Err.Raise Err.Number, Err.source, Err.Description, Err.HelpFile, Err.HelpContext
  Exit Sub
 
does_not_exist:
  Select Case Err.Number
   Case 7874  'Error returned when the dd_columns table does not exist.
    Resume Next
   Case Else
    Err.Raise Err.Number, Err.source, Err.Description, Err.HelpFile, Err.HelpContext
  End Select
 
End Sub

Query for "Unpivotting" a data table (Data_Table). This query assumes the table has a one column primary key (data_table_id) and you desire to unpivot all the other columns.

SELECT dt.data_table_id AS data_table_id
     , ddc.column_name AS column_name
     , DLookUp([ddc].[column_name],"Data_Table","data_table_id = " & dt.data_table_id ) AS column_value 
FROM dd_columns AS ddc
   , Data_Table AS dt

Thanks for the help!