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
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
Hi !
I don't know if it's possible but I need to create in a table as many
records as a number entered in a form. (Example: In a form I write 4
and it creates 4 new records in a table).
In each of those records I need to paste a code of 3 leters and the
year (Example: In the same form I write in the field code ABC, and in
the field year 2006, and it enters those values to each of the 4 new records).
Does anybody know how this can be done ?
Thank you all in advance
Nuno
El miércoles, 4 de enero de 2006 a las 3:23:15 UTC+1, Lyle Fairfield escribió:been able to speed up not only the lookup functions but also the insert into method.
131072 Records in three seconds (as per recommendation by Tom van Stiphout).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.
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
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
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: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).
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
If you have any doubts I leave my email so you can contact me whenever you want.
Jordi,
jordim...@gmail.com
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 286 |
Nodes: | 16 (2 / 14) |
Uptime: | 85:02:46 |
Calls: | 6,495 |
Calls today: | 6 |
Files: | 12,097 |
Messages: | 5,276,965 |