El miércoles, 4 de enero de 2006 a las 3:23:15 UTC+1, Lyle Fairfield escribió:
> 131072 Records in three seconds (as per recommendation by Tom van
> Stiphout).
> Sub AddRecordsDAO()
> Dim z As Long
> DBEngine(0)(0).Execute "DELETE * FROM Test"
> Debug.Print Now() '2006-01-03 21:20:57
> With DBEngine(0)(0)
> .Execute "INSERT INTO Test (Field1, Field2, Field3) VALUES
> (Null, Null, Null)"
> For z = 0 To 16
> .Execute "INSERT INTO Test SELECT Field1, Field2, Field3
> FROM Test"
> Next z
> End With
> Debug.Print Now() '2006-01-03 21:21:00
> End Su

Hello everyone. I have been working on Access for a while and I would like to give my opinion. I'm not an expert about it but I like learning. First of all, if you really want to speed up your applications I heavily recommend this post by harfang.

https://www.experts-exchange.com/articles/1921/Access-Techniques-Fast-Table-Lookup-Functions.html

Here they state a study about how to create fast lookup and other useful functions. Taking a deep look into it, you will see that he actually works with some types recordsets and he explains the performance differences between them. Using it I have been able to speed up not only the lookup functions but also the insert into method.

If you are using a split database (the tables in one access file and the application where the user works in another access file) this is the fastest method I have found so far. The code is the following:

Public Sub Tst()
Dim a As Double
a = Timer
Call Add4
Debug.Print Timer - a
End Sub

Private Sub Add1()
Dim rst As New ADODB.Recordset, i As Long
With rst
..CursorLocation = adUseClient
..Open "SELECT Num1, Num2 FROM Test;", CurrentProject.Connection, adOpenStatic, adLockBatchOptimistic
For i = 0 To 9999
..AddNew Array("Num1", "Num2"), Array(4, 5)
Next i
..UpdateBatch
End With
Set rst = Nothing
End Sub

Private Sub Add2()
Dim db As DAO.Database, i As Long
Set db = CurrentDb()
For i = 0 To 9999
db.Execute "INSERT INTO Test (Num1,Num2) VALUES (4,5);"
Next i
Set db = Nothing
End Sub

Private Sub Add3()
Dim db As DAO.Database, i As Long
Set db = OpenDatabase(Mid(CurrentDb("Test").Connect, 11))
For i = 0 To 9999
db.Execute "INSERT INTO Test (Num1,Num2) VALUES (4,5);"
Next i
Set db = Nothing
End Sub

Private Sub Add4() '0.84375
Dim db As DAO.Database, i As Long
Set db = OpenDatabase(Mid(CurrentDb("Test").Connect, 11))
With db("Test").OpenRecordset
For i = 0 To 9999
..AddNew
!Num1 = 4
!Num2 = 5
..Update
Next i
End With
Set db = Nothing
End Sub

You can directly copy these functions in a standard module and create a form with a button with something like:

Private Sub Command0_Click()
Call Tst
End Sub

In the Sub "Tst" you can simply change the number of "Call Add4" to test all the functions and compare. The time will be displayed in the immediate window. The first two functions are the ones that you have mentioned before so I used them to test. The database only have a table called "Test" with two number fields "Num1" and "Num2". You can test the application in a standard access database where any of the first three functions work correctly (my test showed that "Add3" have the best performance). But where I found the real interest of this test is on split database where speed will be really lower. Try the functions in a split database and you will check the capacity of the "Add4" function. Remember to save the front end application as accde which is a compiled state and will speed up all your code execution.

If you have any doubts I leave my email so you can contact me whenever you want.

Jordi,
jordimarsa96@gmail.com

On Wednesday, July 7, 2021 at 12:16:38 PM UTC-5, Jordi Marsà wrote:
> El miércoles, 4 de enero de 2006 a las 3:23:15 UTC+1, Lyle Fairfield escribió:
> > 131072 Records in three seconds (as per recommendation by Tom van
> > Stiphout).
> > Sub AddRecordsDAO()
> > Dim z As Long
> > DBEngine(0)(0).Execute "DELETE * FROM Test"
> > Debug.Print Now() '2006-01-03 21:20:57
> > With DBEngine(0)(0)
> > .Execute "INSERT INTO Test (Field1, Field2, Field3) VALUES
> > (Null, Null, Null)"
> > For z = 0 To 16
> > .Execute "INSERT INTO Test SELECT Field1, Field2, Field3
> > FROM Test"
> > Next z
> > End With
> > Debug.Print Now() '2006-01-03 21:21:00
> > End Su
> Hello everyone. I have been working on Access for a while and I would like to give my opinion. I'm not an expert about it but I like learning. First of all, if you really want to speed up your applications I heavily recommend this post by harfang.
>
> https://www.experts-exchange.com/articles/1921/Access-Techniques-Fast-Table-Lookup-Functions.html
>
> Here they state a study about how to create fast lookup and other useful functions. Taking a deep look into it, you will see that he actually works with some types recordsets and he explains the performance differences between them. Using it I have been able to speed up not only the lookup functions but also the insert into method.
>
> If you are using a split database (the tables in one access file and the application where the user works in another access file) this is the fastest method I have found so far. The code is the following:
>
> Public Sub Tst()
> Dim a As Double
> a = Timer
> Call Add4
> Debug.Print Timer - a
> End Sub
>
> Private Sub Add1()
> Dim rst As New ADODB.Recordset, i As Long
> With rst
> .CursorLocation = adUseClient
> .Open "SELECT Num1, Num2 FROM Test;", CurrentProject.Connection, adOpenStatic, adLockBatchOptimistic
> For i = 0 To 9999
> .AddNew Array("Num1", "Num2"), Array(4, 5)
> Next i
> .UpdateBatch
> End With
> Set rst = Nothing
> End Sub
>
> Private Sub Add2()
> Dim db As DAO.Database, i As Long
> Set db = CurrentDb()
> For i = 0 To 9999
> db.Execute "INSERT INTO Test (Num1,Num2) VALUES (4,5);"
> Next i
> Set db = Nothing
> End Sub
>
> Private Sub Add3()
> Dim db As DAO.Database, i As Long
> Set db = OpenDatabase(Mid(CurrentDb("Test").Connect, 11))
> For i = 0 To 9999
> db.Execute "INSERT INTO Test (Num1,Num2) VALUES (4,5);"
> Next i
> Set db = Nothing
> End Sub
>
> Private Sub Add4() '0.84375
> Dim db As DAO.Database, i As Long
> Set db = OpenDatabase(Mid(CurrentDb("Test").Connect, 11))
> With db("Test").OpenRecordset
> For i = 0 To 9999
> .AddNew
> !Num1 = 4
> !Num2 = 5
> .Update
> Next i
> End With
> Set db = Nothing
> End Sub
>
> You can directly copy these functions in a standard module and create a form with a button with something like:
>
> Private Sub Command0_Click()
> Call Tst
> End Sub
>
> In the Sub "Tst" you can simply change the number of "Call Add4" to test all the functions and compare. The time will be displayed in the immediate window. The first two functions are the ones that you have mentioned before so I used them to test. The database only have a table called "Test" with two number fields "Num1" and "Num2". You can test the application in a standard access database where any of the first three functions work correctly (my test showed that "Add3" have the best performance). But where I found the real interest of this test is on split database where speed will be really lower. Try the functions in a split database and you will check the capacity of the "Add4" function. Remember to save the front end application as accde which is a compiled state and will speed up all your code execution.
>
> If you have any doubts I leave my email so you can contact me whenever you want.
>
> Jordi,
> jordim...@gmail.com

Good information on that fast lookup page.
The comparison of DAO seek and ADO was informative. In most cases DAO is faster on Access databases the ADO.