Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Posts
    18

    Question Unanswered: put arrays into recordsets

    I have written code to get SAP data into an array. I have already defined a table from SAP information, but now I need to add the data from the array into the table.
    At first I had a textlike object from which I wrote field after field, but that took too much time so now I'm looking to add the whole array into a recordset in one move.

    Does anyone have a bit of code example to do this? I know you can put arrays into recordsets, but all the examples I could find so far is with single rows.

    Thanks for your help!

    I have tried the following code, but it generates an error at the line where I try to add records to the recordset (as indicated)
    The filling arrCompanycodes worked fine, this is what I got from the immediate:
    ?arrCompanycodes(0,4)
    NL01


    Dim arrCompanycodes As Variant
    Dim arrFields As Variant
    Dim rs As New ADODB.Recordset
    Dim cn As ADODB.Connection
    Dim rs2 As New ADODB.Recordset

    Set cn = CurrentProject.Connection

    rs.Open "Select * from CompCodes", cn, adOpenDynamic, adLockOptimistic
    arrCompanycodes = rs.GetRows

    arrFields = Array("newCC", "Companyname") 'column names

    rs2.Open "Select * from test", cn, adOpenDynamic, adLockOptimistic

    rs2.AddNew arrFields, arrCompanycodes 'this one generates an error
    rs.Close
    rs2.Close

  2. #2
    Join Date
    Dec 2002
    Posts
    4
    I've just written something simpler in Access97 - writing an array to an output table where each field is similarly named -Address1, Address2 ... address5.

    The trick is to create an array of controls, rather like a control array, where only the field suffix changes. It is used with an index, and the output field would be referenced as '"Address" & cstr(index)'

    Here's a code snippet:

    ' this bit of code simulates a control array
    ' (i.e. rst2!Address1, rst2!Address2, rst2!Address3 etc.)
    ' and stores the appropriate address array element to each
    For bytAddressLineIndex = 1 To bytAddressLines
    rst2("Address" & CStr(bytAddressLineIndex)).Value = AddressArray(bytAddressLineIndex)
    Next

    Hope this helps.

  3. #3
    Join Date
    Dec 2002
    Posts
    18
    Thanks but, this still deals with it record by record instead of the whole array at once and with 30000 records this is too slow. Besides that I cannot rename fields like that, they have to keep the same name as they have to go back to SAP one day.

    I already made this code to add the rows one by one (this is example code, for real I have two arrays, one with the fieldnames and one 2 dimensional one with the data and only one empty table):

    Dim arrCompanycodes As Variant
    Dim arrFields As Variant
    Dim rs As New ADODB.Recordset
    Dim cn As ADODB.Connection
    Dim rs2 As New ADODB.Recordset
    Dim i As Integer
    Dim l As Long
    Set cn = CurrentProject.Connection

    rs.Open "Select * from CompCodes", cn, adOpenDynamic, adLockOptimistic
    arrCompanycodes = rs.GetRows

    arrFields = Array("newCC", "Companyname") 'column names

    rs2.Open "Select * from test", cn, adOpenDynamic, adLockOptimistic
    rs2.RecordCount

    For l = 0 To UBound(arrCompanycodes, 2)
    rs2.AddNew
    For i = 0 To UBound(arrFields)
    rs2(arrFields(i)) = arrCompanycodes(i, l)
    Next
    rs2.Update
    Next
    rs.Close
    rs2.Close
    Last edited by Poppekop; 12-10-02 at 08:10.

  4. #4
    Join Date
    Dec 2002
    Posts
    4
    Sorry, I'm out of my depth on this one, although I do enclose some code - can't remember where I got it - that does a scatter/gather (which will mean something to those of us brought up on dBase and Clipper).



    Option Compare Database 'Use database order for string comparisons
    Option Explicit


    Sub ArrayToRSRec(a() As Variant, rs As Recordset, Fieldnames)
    '
    ' Similar to xBase Scatter/Gather
    '
    ' Copies the fields from an array to a recordset record. It is up to the
    ' calling function to ensure that the array contains enough elements,
    ' that the recordsetset is on a record and has executed either RS.Edit or RS.AddNew.
    ' It is also up to the calling function to do RS.Update. The calling function
    ' must make sure that it doesn't use CopyArrayToRSRecord to copy
    ' OLE/Binary fields.
    '
    ' A() Any 1-dimensional array.
    ' RS Any Recordset that is currently positioned on a record.
    ' FieldNames If Null or Empty String, copies all fields.
    ' If non-blank, copies those fields listed.
    ' e.g. "Order ID, Customer Name, Sales Amount"
    '
    ' Calling convention:
    ' Redim A(20) as Variant
    ' ArrayToRSRec A(), rs, "Customer ID, City , region "
    ' or ArrayToRSRec A(), rs, Null <-- copies all fields
    '
    ' WARNING: This function will crash if it tries to copy OLE or Binary fields.
    '
    Dim LB As Integer, i As Integer, WC As Integer
    LB = LBound(a)
    WC = CountCSVWords(Fieldnames)
    If WC = 0 Then
    For i = 0 To rs.Fields.Count - 1
    rs(i) = a(LB + i)
    Next i
    Else
    For i = 1 To WC
    rs(Trim(GetCSVWord(Fieldnames, i))) = a(LB - 1 + i)
    Next i
    End If
    End Sub



    Sub RSRecToArray(rs As Recordset, a(), Fieldnames)
    '
    ' Similar to xBase Scatter/Gather
    '
    ' Copies the fields from a recordset record into an array. It is up to the
    ' calling function to ensure that the array contains enough elements,
    ' that the recordset is on a record, and that it doesn't try to copy OLE/Binary
    ' fields.
    '
    ' RS Any Recordsetset that is currently positioned on a record.
    ' A() Any 1-dimensional array.
    ' FieldNames If Null or Empty String, copies all fields.
    ' If non-blank, copies those fields listed.
    ' e.g. "Order ID, Customer Name, Sales Amount"
    '
    ' Calling convention:
    ' Redim A(20) as Variant
    ' RSRecToArray rs, A(), "Customer ID, City , region "
    ' or RSRecToArray rs, A(), Null <-- copies all fields
    '
    ' WARNING: This function will crash if it tries to copy OLE or Binary fields.
    '
    Dim LB As Integer, i As Integer, WC As Integer
    LB = LBound(a)
    WC = CountCSVWords(Fieldnames)
    If WC = 0 Then
    For i = 0 To rs.Fields.Count - 1
    a(LB + i) = rs(i)
    Next i
    For i = 0 To UBound(a) - 1
    Debug.Print a(i)
    Next
    Else
    For i = 1 To WC
    a(LB - 1 + i) = rs(Trim(GetCSVWord(Fieldnames, i)))
    Next i
    ' Print out the contents of the Array A()
    For i = 0 To WC - 1

    Debug.Print a(i)
    Next i
    End If
    End Sub


    Function CountCSVWords(S) As Integer
    '
    ' Counts words in a string separated by commas.
    '
    Dim WC As Integer, Pos As Integer
    If VarType(S) <> 8 Or Len(S) = 0 Then
    CountCSVWords = 0
    Exit Function
    End If
    WC = 1
    Pos = InStr(S, ",")
    Do While Pos > 0
    WC = WC + 1
    Pos = InStr(Pos + 1, S, ",")
    Loop
    CountCSVWords = WC
    End Function

  5. #5
    Join Date
    Dec 2002
    Posts
    18

    Unhappy

    Don't worry, no one in the whole wide internet world can give me an answer:-'( I guess it is just not possible. Your code contains loops as well and that takes up too much time. Thanks for the effort though!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •