Excel VBA + Massive Array + SQL INSERT = HELP!

[Avatar]

Greetings,

I have an array (dimensions: 1.5mil,14) containing data entries that i need to INSERT into an Access 2003 table. I am hoping to do this as a single SQL string... Is this possible?

below is the code in a nut shell.

Sub sbRun()
 Dim aryDataSet()
 Dim i As Long, _
     lngTopRow As Long
     i = 1: lngTopRow = 1
 Dim StopToCorrect As Boolean
 
 Call ImportDataToArray(aryDataSet(), lngTopRow)
 
 For i = 1 To UBound(aryDataSet, 1)
  Call FormatDataSet(aryDataSet(i, 1), aryDataSet(i, 2), aryDataSet(i, 3), aryDataSet(i, 4), aryDataSet(i, 5), aryDataSet(i, 6), aryDataSet(i, 7), aryDataSet(i, 8), aryDataSet(i, 9), i + lngTopRow - 1, StopToCorrect)
  If StopToCorrect = True Then Exit Sub
 Next i
 
 'kept seperate as code can be stopped to manually correct original data set

 For i = 1 To UBound(aryDataSet, 1)
  Call INSERT_INTO_DB_ADO(aryDataSet(i, 1), aryDataSet(i, 2), aryDataSet(i, 3), aryDataSet(i, 4), aryDataSet(i, 5), aryDataSet(i, 6), aryDataSet(i, 7), aryDataSet(i, 8), aryDataSet(i, 9))
 Next i
End Sub

Sub INSERT_INTO_DB_ADO(ByRef varDate, _
                       ByRef varSubPrefix, _
                       ByRef varSubLoc, _
                       ByRef varAccNo, _
                       ByRef varAccNam, _
                       ByRef varNINO, _
                       ByRef varDOB, _
                       ByRef varSubDate, _
                       ByRef varSubAmount)
 Dim Conn As New ADODB.Connection
 Dim Rst As New ADODB.Recordset
 Dim MySQL As String
 Dim AccessConnect As String
 
 AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
                 "Dbq=" & DBName & ";" & _
                 "DefaultDir=" & DatabaseDIR & ";" & _
                 "Uid=Admin;Pwd=" & strPassword & ";"
 Conn.Open AccessConnect
 
 MySQL = _
  "INSERT INTO tbl_RenewalImport ( DateOfReport, AccountNumber, MemberName, NINO, DOB, SubPrefix, SubDate, SubAmount, SubLocation ) " & _
  "SELECT #" & varDate & "#, '" & varAccNo & "', '" & varAccNam & "', '" & varNINO & "', #" & varDOB & "#, " & varSubPrefix & ", #" & varSubDate & "#, '" & varSubAmount & "', '" & varSubLoc & "';"
 'MsgBox MySQL
 
 Set Rst = Conn.Execute(MySQL)
 
 Conn.Close
 Set Rst = Nothing
 Set Conn = Nothing
 
End Sub

How could i do this large scale if i wanted to import all 1.5mil entries in one go? - 1.5mil entries 1 at a time would take hours/days(!?!)...

Please Help!

 

[Avatar]

oooh.. I should probably add there's no reason why i can't pass the whole array into the INSERT_INTO_DB_ADO sub. If i am forced to do it 1 record at a time then i would obviously do this and loop inside the DB connection rather than close it each time..

..When i wrote this i wasn't aware of the size of the array and thought it would only have a handful of entries.

 

[Peter_SSs]

Did you mean to post this in the Excel Questions forum?
Would you like me to move it to the Access forum?

 

[Avatar]

I think it's in the correct place? All the code is in Excel, only the database is in Access, but that is acting solely as a data store.

 

[RoryA]

If you are dealing with an array, I think your best bet would be to open the connection, issue a BeginTrans command, then write the data before finally doing a committrans.