I have a table that will hold header records. These are going to be used to insert into a recordset which then will be populated with the data from a separate csv file. The number of fields will vary with each header record. The header table has 3 fields: HeaderID, FieldName and FieldOrder. For the first two header records, the data would look like this:
HeaderID FieldName FieldOrder
1 HeaderID 10
1 AcctName 20
1 AcctNum 30
2 HeaderID 10
2 AcctName 20
2 AcctNum 30
2 AcctMgr 40
2 AcctRegion 50
I need to create a recordset using FieldName as the Column Headings in my new recordset. For the first Header record, It should look like this:
HeaderID AcctName AcctNum
It seems like it should be a simple problem, but I haven't been able to get anything to actually work. I've attached my latest attempt.
'pull the header record data
strSQL = " SELECT tblHeader.*"
strSQL = strSQL & " From tblHeader"
strSQL = strSQL & " WHERE (((tblHeader.HeaderID)=" & rptNum & "))"
strSQL = strSQL & " ORDER BY tblHeader.FieldOrder;"
Set rc = db.OpenRecordset(strSQL, dbOpenDynaset)
rCnt = rc.RecordCount
'test: 21 records for header 004
For i = 1 To rCnt
Set rc.Fields(i).Name = rc.Fields("FieldName")