Speed up write from Excel to Access
The procedure below is run in an Excel module. In it several dependent variables which are functions of ten independent variables are calculated and written to Access. There are ten nested For loops; therefore, this procedure calculates tens of millions of values and writes millions of records to Access. This takes FOREVER so I'd like to employ the fastest way to write to Access.
I'm currently using dbs.CurrentDb.Execute "INSERT INTO ... VALUES ...".
aikimark suggested using recordsets instead of INSERT INTO. The new procedure using recordsets is in the code snippet. This procedure is MUCH slower than the same procedure using INSERT INTO ... VALUES ....
Is this to be expected or am I doing something wrong? Is there a way to speed up the code in the code snippet?
NOTE: I tried CSV files which are faster than either the recordset approach or the INSERT INTO approach but for some reason some decimal values are truncated to integer values which isn't acceptable.
I'm currently using dbs.CurrentDb.Execute "INSERT INTO ... VALUES ...".
aikimark suggested using recordsets instead of INSERT INTO. The new procedure using recordsets is in the code snippet. This procedure is MUCH slower than the same procedure using INSERT INTO ... VALUES ....
Is this to be expected or am I doing something wrong? Is there a way to speed up the code in the code snippet?
NOTE: I tried CSV files which are faster than either the recordset approach or the INSERT INTO approach but for some reason some decimal values are truncated to integer values which isn't acceptable.
Sub CostLoop()
Set g_objConn = New ADODB.Connection
g_objConn.Open g_sConnectionString
Set rs = New ADODB.Recordset
rs.Open "tbl", g_objConn, adOpenDynamic, adLockPessimistic
For iCounter1 = 1 to (...)
For iCounter2 = 1 to (...)
For iCounter3 = 1 to (...)
...
For iCounter10 =
iRecordCounter = iRecordCounter + 1
If iRecordcounter = 1 Then g_objConn.BeginTrans
dOutput1 = (...)
dOutput2 = (...)
dOutput3 = (...)
...
dOutput10 = (...)
'SETUP RECORDSET TO ADD A NEW RECORD
rs.AddNew
'ASSIGN FIELDS IN TABLE rs!<columnname in table> = <value>
rs!Variable1 = iCounter1
rs!Variable2 = iCounter2
rs!Variable3 = iCounter3
rs!Variable4 = iCounter4
rs!Variable5 = iCounter5
rs!Variable6 = iCounter6
rs!Variable7 = iCounter7
rs!Variable8 = iCounter8
rs!Variable9 = iCounter9
rs!Variable10 = iCounter10
rs!Output1 = dOutput1
rs!Output2 = dOutput2
rs!Output3 = dOutput3
rs!Output4 = dOutput4
rs!Output5 = dOutput5
rs!Output6 = dOutput6
rs!Output7 = dOutput7
rs!Output8 = dOutput8
rs!Output9 = dOutput9
rs!Output10 = dOutput10
'ISSUE THE ADD
rs.Update
If iRecordCounter = 100 Then
g_objConn.CommitTrans
iRecordCounter = 0
End If
Next iCounter10
...
Next iCounter3
Next iCounter2
Next iCounter1
g_objConn.CommitTrans
rs.Close
Set rs = Nothing
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
inthedark
Good point aikmark, it would seem to me that an entire spreadsheet can only be a few thousand rows which would import into a database within a few seconds, I would estimate about 200 rows per second.