[AccessD] Import columns

Hi Oleg

Assuming that your 12 fields are juxtaposed, why not just loop through
the records and the fields:

Public Function AppendRecords()

  ' First field of source table to pick data.
  Const clngRst1Offset As Long = 4  ' Adjust!
  ' First field of target table to insert data.
  Const clngRst2Offset As Long = 1  ' Adjust!
  ' Max count of fields to use in target table.
  Const clngRst2Max    As Long = 12

  Dim cnn        As ADODB.Connection
  Dim rst1       As ADODB.Recordset
  Dim rst2       As ADODB.Recordset
  Dim strSQL1    As String
  Dim strSQL2    As String
  Dim lngField1  As Long
  Dim lngField2  As Long
  Dim lngField   As Long
  
  Set cnn = CurrentProject.Connection
  Set rst1 = New ADODB.Recordset
  Set rst2 = New ADODB.Recordset
  
  strSQL1 = "Select * From Hrsqd"
  strSQL2 = "Select Top 1 * From Hrsqd_Full"
  
  rst1.Open strSQL1, cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
  rst2.Open strSQL2, cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
  
  ' Number of fields to pick from source table.
  lngField1 = rst1.Fields.Count - clngRst1Offset
  ' First field in target table to insert data.
  lngField2 = clngRst2Offset + clngRst2Max - lngField1
  
  Do While Not rst1.EOF
    rst2.AddNew
      For lngField = 0 To lngField1 - 1
        rst2.Fields(lngField2 + lngField).Value = _
          rst1.Fields(clngRst1Offset + lngField).Value
      Next
    rst2.Update
    rst1.MoveNext
  Loop
  
  rst2.Close
  rst1.Close
  
  Set rst2 = Nothing
  Set rst1 = Nothing
  Set cnn = Nothing

End Function

/gustav


> Hey Group, happy holidays fro those of u in US. I am supposed to count the
> number of columns and based on the result import the data into a table
> with month names. The last column always contains December
> Am I on the right track ?
> This is what I've done so far ---

> Private Sub Command1_Click()
> Dim cnn As ADODB.Connection
> Dim rst1 As New ADODB.Recordset
> Dim SQL1 As String, SQL2 As String
> Dim a As Integer 'number of columns
> Dim b As Integer
> Dim Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec

> Set cnn = CurrentProject.Connection

> SQL1 = "Select * From Hrsqd"
> rst1.Open SQL1, cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

> a = rst1.Fields.Count 'number of fields
> b = a - 2 ' number of month fields



> If b = 6 Then 'Jul - Dec
> Do Until rst1.EOF
> Jul = rst1.Fields(3)
> Aug = rst1.Fields(4)
> Sep = rst1.Fields(5)
> Oct = rst1.Fields(6)
> Nov = rst1.Fields(7)
> Dec = rst1.Fields(8)
> Loop
> End If

> If b = 5 Then 'Aug - Dec
> Do Until rst1.EOF
> Aug = rst1.Fields(3)
> Sep = rst1.Fields(4)
> Oct = rst1.Fields(5)
> Nov = rst1.Fields(6)
> Dec = rst1.Fields(7)
> Loop
> End If

> If b = 4 Then 'Sep - Dec
> Do Until rst1.EOF
> Sep = rst1.Fields(3)
> Oct = rst1.Fields(4)
> Nov = rst1.Fields(5)
> Dec = rst1.Fields(6)
> Loop
> End If

> rst1.Close

> SQL2 = "INSERT Into HRSQD_Full (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug,
> Sep, Oct, Nov, Dec) Values (" & Jan & "," & Feb & "," & Mar & "," & Apr &
> "," & May & "," & Jun & "," & Jul & "," & Aug & "," & Sep & "," & Oct &
> "," & Nov & "," & Dec & ")"

> MsgBox a

> End Sub