[AccessD] Import columns

Hi Gustav
I envy you :))
Unfortanetely, i had used my old code (which is 5 times longer) one caz I
also have to import to front clumns as well

> 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
>
> --
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com



-----------------------------------------
Make Money and Find Love at Finally.com
http://www.finally.com/?link=webmail