ADO speeds up open table access

Question

What is the best way to open a table with ADO and Access for rs.AddNew and rs.Update based on data write speed?

Do I need to use a specific cursor or a specific method?

I am using an ADO connection with Jet from VB6.

Answer 1

My two suggestions:

• Open Recordset as adOpenStatic to minimize overhead to track changes to the table that other users can make.

• Wrap some .AddNew operations in a transaction by executing cn.BeginTrans before the insert package and cn.CommitTrans afterwards.

Edit

In response to a comment from @ Bob77 in which he said:

Throwing Begin / End Trans around a series of arbitrary update operations does not provide any internal performance benefits, and in most cases this should exacerbate the situation.

The following VBScript test results clearly show that migrating a transaction attachment package can significantly improve performance when working with Jet / ACE databases.

 Option Explicit Dim con, rst, t0, i, n, s Const adUseClient = 3 Const adOpenStatic = 3 Const adLockOptimistic = 3 Const useTransaction = False t0 = Timer n = 1000 Set con = CreateObject("ADODB.Connection") con.CursorLocation = adUseClient con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Y:\adoTimeTest.accdb;" Set rst = CreateObject("ADODB.Recordset") rst.Open "SELECT * FROM tbl", con, adOpenStatic, adLockOptimistic If useTransaction Then con.BeginTrans End If For i = 1 to n rst.AddNew rst("ItemName").Value = "Item_" & i rst("SeqNo").Value = i rst.Update Next If useTransaction Then con.CommitTrans End If rst.Close Set rst = Nothing con.Close Set con = Nothing s = "Added " & n & " rows in " & Round(Timer - t0, 1) & " seconds with" If Not useTransaction Then s = s & "out" End If s = s & " transaction." Wscript.Echo s 

The table structure [tbl] is equal to

 ID - AutoNumber, Primary Key ItemName - Text(255), Indexed (duplicates OK) SeqNo - Long Integer, Indexed (no duplicates) 

Test 1: useTransaction = False

The [tbl] table is empty, and the .addcb file has recently been compressed.

 Y:\>cscript /nologo adoTimeTest.vbs Added 1000 rows in 103.9 seconds without transaction. 

Test 2: useTransaction = True

The [tbl] table was empty, and the .addcb file was compressed again.

 Y:\>cscript /nologo adoTimeTest.vbs Added 1000 rows in 4.9 seconds with transaction. 

Edit

In response to the following comment from @ Bob77:

I will try again to open the database for exclusive access.

Additional tests using ODBC and exclusive access:

 con.Open "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=Y:\adoTimeTest.accdb;Exclusive=1;Uid=admin;Pwd=;" 

Test 3: useTransaction = False

The [tbl] table is empty, and the .addcb file has recently been compressed.

 Y:\>cscript /nologo adoTimeTest.vbs Added 1000 rows in 26.5 seconds without transaction. 

Test 4: useTransaction = True

The [tbl] table was empty, and the .addcb file was compressed again.

 Y:\>cscript /nologo adoTimeTest.vbs Added 1000 rows in 6.1 seconds with transaction.