Fastest way to add an Array to a table

[Karja]

Goodmorning,

I've got a 2d-array with about 1000 rows and 5 columns, now I want to write this into the database. Now I just have a loop through the Array and write each line seperately to the database.

for x=1 to ubound(array)
rs.adddnew
  rs![Field1] = array(x,1)
  rs![Field2] = array(x,2)
  rs![Field3] = array(x,3)
  .....
rs.update
next x

It takes about 20 seconde. Can't this be done any faster. Like moving the array to a recordset and append the whole record set to the table? Any suggestions? Any help appreciated!

Kind regards, Karja

 

[MajP]

I would think something like this will be faster.

Public Sub insertArray(myArray() As String)
  Dim i As Integer
  Dim strSql As String
  Dim val1 As String
  Dim val2 As String
  Dim val3 As String
  DoCmd.SetWarnings (False)
  For i = 1 To UBound(myArray, 2)
     val1 = myArray(1, i)
     val2 = myArray(2, i)
     val3 = myArray(3, i)
    strSql = "insert into tblArray (fldOne,fldTwo,fldThree) values('" & val1 & "', '" & val2 & "', '" & val3 & "')"
    DoCmd.RunSQL strSql
  Next i
 DoCmd.SetWarnings (True)
End Sub

 

[MajP]

here was my test

Public Sub test()
  Dim myArray(1 To 3, 1 To 3) As String
  myArray(1, 1) = "A"
  myArray(2, 1) = "B"
  myArray(3, 1) = "C"
  myArray(1, 2) = "D"
  myArray(2, 2) = "E"
  myArray(3, 2) = "F"
  myArray(1, 3) = "G"
  myArray(2, 3) = "H"
  myArray(3, 3) = "I"
  Call insertArray(myArray)
End Sub

my table and results:
fldOne fldTwo fldThree
A B C
D E F
G H I

 

[Karja]

Thanks for your answer and effort. Your suggestion works fine but is not any faster. I've done some rewriting as the function you're supplying might come in handy. Here the altered code which makes it more costumizable.
Kind regards, Karja

Public Sub insertArrayIntoTable(myArray() As Variant, Flds As String, desTable As String)
Dim vls As String
For x = 1 To UBound(myArray, 1)
    If myArray(x, 1) = "[not valid]" Then GoTo volgende
    vls = ""
    For y = 1 To UBound(myArray, 2)
        vls = vls & "'" & myArray(x, y) & "'"
        If Not y = UBound(myArray, 2) Then vls = vls & ", "
    Next y
    vls = "(" & vls & ")"
    SQLa = "INSERT INTO " & desTable & " " & Flds & " VALUES " & vls
    Execute SQLa
volgende:
Next x
End Sub

 

[MichaelRed]

Given the starting point

" ... got a 2d-array ... "

there are not very many approaches to appending the content to a recordset/table.

Further, given that you are posting this in an 'SQL' forum, it seems to follow that you want/expect/need an 'SQL' answer.

A different approach MIGHT be to provide some additional info on the overall process (e.g. how did you get the 5K items to begin with?) If it was data entry, why weren't the 1K individual records appended as they were entered? If they are 'calculated' why do they need to be in a table anyway?

Further, appending 1K records using the methods shown here in should not be a terrible consumption of resources (in the order of a second or two?) and if this is a significant problem (much more than the second or two) there must be other issues which need to be resolved?

Lastly, I'm absoloutley positive and sure beyond doubt that [cplor blue]MajP[/color] soesn't realy need your 'refinement' of his code. Llike most of the content providers he is attempting to provide you with the corre content of the soloution, N O T a canned and resuable routine for the lifetime of the product(s). If you are truly attempting to improve on his work, I would suggest that you consider the possability of providing V&V on the data such as assuring that each element of the array is of the propper data type for the recordset field you are apprnding it to. However even this is what is normally not included by responders in these fori.

I could carry this rant further but, perhaps, the point is sufficiently made?



MichaelRed

 

[Karja]

Yes, I got your point. I thought it would be nice to work with his code and just let him know. The reason why the data is in an array is dat many calculations are made with it which were already written in VBA. Now I am working on storing the data. This the story. No higher goals of what soever. Thanks for your point of view.

 

[MajP]

Actually I really appreciate your code. That is exactly what I try to show and strive for on this site. I always try to show well encapsulated code that has a lot of flexibility. That type of function looks like something I would write. I only wish that I would have thought of it. I often post solutions using custom classes and object oriented design. Although this requires a little overhead, the reuse and useability is far greater.