VBA for quickly entering many records in an access database

Question

OK, so I have a spreadsheet that produces a fairly large amount of records (~ 3500)

I have the following script that inserts them into my db access:

Sub putinDB() Dim Cn As ADODB.Connection, Rs As ADODB.Recordset Dim MyConn, sSQL As String Dim Rw As Long, c As Long Dim MyField, Result Dim x As Integer Dim accName As String, AccNum As String, sector As String, holding As String, holdingvalue As Double, holdingdate As Date theend = lastRow("Holdings", 1) - 1 'Set source MyConn = "S:\Docs\Harry\Engine Client\Engine3.accdb" 'Create query Set r = Sheets("Holdings").Range("a2") x = 0 Do Application.StatusBar = "Inserting record " & x + 1 & " of " & theend accName = r.Offset(x, 0) AccNum = r.Offset(x, 4) sector = r.Offset(x, 2) holding = r.Offset(x, 1) holdingvalue = r.Offset(x, 3) holdingdate = r.Offset(x, 5) sSQL = "INSERT INTO Holdings (AccName, AccNum, Sector, Holding, HoldingValue, HoldingDate)" sSQL = sSQL & " VALUES ('" & Replace(accName, "'", "''") & "', '" & AccNum & "', '" & sector & "', '" & Replace(holding, "'", "''") & "', '" & holdingvalue & "', #" & holdingdate & "#)" Debug.Print (sSQL) 'Create RecordSet Set Cn = New ADODB.Connection With Cn .Provider = "Microsoft.ACE.OLEDB.12.0" .CursorLocation = adUseClient .Open MyConn Set Rs = .Execute(sSQL) End With x = x + 1 Loop While r.Offset(x, 0) <> "" Or x < 15 Application.StatusBar = False End Sub 

The problem is that it cycles through each record one by one, rebuilds and executes the query every time, which leads to very slow execution (about 2-3 records per second on my PC).

Is there a way for vba to insert the entire range into the database in one pass without looping? Thanks

Answer 1

Good, stupid of me. After messing around a bit, it turns out that posting

 Set Cn = New ADODB.Connection With Cn .Provider = "Microsoft.ACE.OLEDB.12.0" .CursorLocation = adUseClient .Open MyConn End With 

a bit outside the loop makes it much faster.

Answer 2

The answer you provided should improve the situation a bit, since you only need to parse the connection once, but the code is still inefficient. You really only want to write your recordset once with all the data, and not like that. I always prefer to work on the Access side to pull information from Excel, to resist access to Access from Excel, but I believe that we can use either for this scenario.

In this case, you better use the DAO for ADO and work with Transaction , in fact, you are still fixated on a set of records, but the actual act of writing data does not happen until you commit at the end, so it is much faster.

This is a very simple example from Access so you can try:

 Private Sub TestTrans() Dim wksp As DAO.Workspace Dim rs As DAO.Recordset Set wksp = DBEngine.Workspaces(0) 'The current database wksp.BeginTrans 'Start the transaction buffer Set rs = CurrentDb.OpenRecordset("Table1", dbOpenDynaset) Do 'Begin your loop here With rs .AddNew !Field = "Sample Data" .Update End With Loop 'End it here wksp.CommitTrans 'Commit the transaction to dataset End Sub