dynamic columns for adding recs to access table

[bbxrider]

this is for an access 2016 db I have say 50 tables that have a total of say 100 unique columns so I can create a blank table with all 100 columns

so table 1 might have cols 1, 10, 99, table 2 might have cols 2, 15, 27, etc I would prefer to programmically iterate through the 50 tables in the db and as I read the records for each table dynamically associate the col name and its data so I can populate a 100 column target record columns with whatever column data there is in current table and write out all the records. naturally there will be many blank fields in the 100 col record. I know how to iterate through all tables and then get all the column names in each table, and get the table records data but can't figure out how to dynamically create the output records, actually not sure if it possible? If possible can somebody help with enough of a sample to get stated or links of course

see below for code I am attempting so far

Sub makeTable()

Dim db As DAO.Database

Dim tdf As DAO.TableDef

Dim fldSource As DAO.Field

Dim fldTarget As DAO.Field

Dim rstTable As DAO.Recordset

Dim rstTargetTable As DAO.Recordset

Dim rstSourceTable As DAO.Recordset

Dim columnCount As Integer

Dim colname, sheetname As String



Set db = CurrentDb

'this is the table with universe all columns to populate from source tables

Set rstTargetTable = db.OpenRecordset("master")

For Each tdf In db.TableDefs

' iterate through all the potential source data tables

If (tdf.Name Like "someID*") Then

Set rstSourceTable = db.OpenRecordset(tdf.Name)

Do While Not rstSourceTable.EOF

rstSourceTable.Next

rstTargetTable.AddNew

For Each fldSource In rstSourceTable.Fields

For Each fldTarget In rstTargetTable.Fields

If fldSource.Name = fldTarget.Name Then

'??how to dynamically match target column with source column data

rstTargetTable!matchingColName = rstSourceTable!currColName.Value

End If

Next 'next field in target table record

Next 'field in current source table record

rstTargetTable.Update

End 'next record in source table

rstSourceTable.Close

Next 'next source table

End Sub

 

[Cronk]

It would be a lot easier to use queries to make tables and add records to them

Select Field1, Field2 INTO tblNewTable from tblOriginalTable

However I have trouble conceiving why you would need to duplicate existing data. Smells like an 'Excel' design.

 

[MajP]

Break this up into digestable modular parts. Too many loops going on
'Get Your source and target. Pass that to another sub
The sql version should work my concern was the possiblity of fields in source not matching fields in target. Provided a recordset version to show you do not have to find matching fields.

Public Sub LoopSourceTables
  Dim db As DAO.Database
  Dim tdf As DAO.TableDef
  Dim fldSource As DAO.Field
  Dim rstTable As DAO.Recordset
  Dim rstTargetTable As DAO.Recordset
  Dim rstSourceTable As DAO.Recordset
  
  Set db = CurrentDb
 'this is the table with universe all columns to populate from source tables
  Set rstTargetTable = db.OpenRecordset("master")
  For Each tdf In db.TableDefs
    ' iterate through all the potential source data tables
     If (tdf.Name Like "someID*") Then
       Set rstSourceTable = db.OpenRecordset(tdf.Name)
       'updatefromSource (RstSourceTable, RstTargetTable)
       'UpdateFromSource2 (tdf.name,"Master) 
    end if
  next Tdf
end sub

Public Sub UpdateFromSource2(SourceTableName as String, TargetTableName as String)
  dim strSql as String
  strSql = "Insert INTO " & TargetTableName & "Select * from " & SourceTableName
  currentDB.execute StrSql
end sub

Recordset version

Public Sub UpdateFromSource(RstSourceTable as dao.recordset, RstTargetTable as dao.recordset)
Do While Not rstSourceTable.EOF
  rstTargetTable.AddNew
    For Each fldSource In rstSourceTable.Fields
      fldName = fldSource.name
      'If you are highly unlikely to ever have a field in the source and not in the target
      'then do away with checking for a match.  Put in some error checking and let if fail if
      'the names do not match. Then do a resume next.  I did not add error checking
      If FldInTarget(fldName) then
        RsTargetTable.fields(fldName) = FldSource.value
      end if
    next fldSource
  rstTargetTable.update
End If

End Sub

Public Function FldInTarget(TargetRs as dao.recordset, fldName As String) as boolean
  dim fld as dao.field
  for each fld in TargetRs.fields
    if fld.name = fldName then
      FldInTarget = True
      exit Function
    end if
  next fld
end function

On the SQL version I am pretty sure if you do an insert select and the destination has more fields than the source, the matching fields will align and the other fields just are null. I did not test that, but I believe that is how it works.

 

[bbxrider]

thanks for the reply
yes good sense of smell. data is originally coming into source tables from spreadsheets

when you suggest field1 field2 are they the actual column names?

if so thats what I want to avoid. wanting to use table properties to
put actual column names into variables and then use the variables
as the means to match whatever columns there are in the current
source table to the same columns in the target table

i didn't know you can combine sql and dao syntax

 

[bbxrider]

super thanks to MajP for your thoughtful, super comprehensive and super fast reply. I wish I could code up as fast you. And good advice about breaking up into modular design.

with a couple of tweaks, spelling etc. its working.
I will have to add some error trapping probably just debug.print to notify of missing columns in my master target table or inconsistent column names in my source tables. currently I have about 20 source tables and end up with about 5000 rows so its not a big deal to just rerun after data scrubbing from errors in previous run.

don't have time right now but will also test the sql version and post back that result.

 

[MajP]

I believe the SQL version will work as long as the names are correct and there are no fields in the source that are not in the target. I cannot check because do not have access or other db on this computer:
"Insert INTO TargetTable Select * from SourceTable"
However, normally you define your columns. Suppose your source table has fields lastname, firstname, DOB. You would want to make the following string
"Insert INTO TargetTable (lastname, firtname, DOB) Select lastName, firstName, DOB from SourceTable
Untested as I said, but this would be the idea of how to make that string given the source table.

Public Function MakeInsert(RstSourceTable as dao.recordset, TargetTableName as string) as string
  dim fld as dao.field
  dim strSql as string
  dim strFlds as string
  dim strColumns as string
  strSql = "INSERT INTO " & TargetTableName
  for each fld i
    if strFlds = "" then
      strFlds = fld.name
    else
      strFlds = strFlds & "," & fld.name
    end if
  next fld
  strColumns = "(" & strFlds & ")"
  strSql = strSql & " " & strColumns & " SELECT " & strFlds & " FROM " & RstSourceTable.name
  MakeInsert = strSql
end function

And good advice about breaking up into modular design

This is one of the most important things in writing good code. "write it once and use it everywhere." Also much easier to debug. When you write code ask yourself if you can design it to be used again. So now by breaking this up you have generic code to loop the tables, see if a field exists in a table, update matching fields in a target given a source and target, make an insert from the fields in a table. These can all be reused, just pass in different arguments.

 

[Pat Hartman]

Now that you have an answer, I'm going to suggest that you rethink the process. Are you aware that tables and queries are interchangeable for most purposes so making temp tables as you are doing is rarely necessary and simply creates bloat.

Also, if this is an application, you are building for someone else, it is really poor practice to be creating or modifying objects in design view in the FE. If this is just for your use, then do what you want.

 

[Uncle Gizmo]

associate the col name and its data

A few years ago I created a tool for converting incorrectly formatted tables, usually tables imported from Excel, into a form more suitable for use in MS Access.

The tool is here:-


http://www.niftyaccess.com/normalization-tool/

along with more details on how it works and on the excel issue...



Sent from my SM-G925F using Tapatalk