• Add a defined number of records to a table

    From =?UTF-8?B?Sm9yZGkgTWFyc8Og?=@21:1/5 to All on Wed Jul 7 10:08:03 2021
    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()

    End Sub

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From =?UTF-8?B?Sm9yZGkgTWFyc8Og?=@21:1/5 to All on Wed Jul 7 10:16:35 2021
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Ron Paii@21:1/5 to All on Mon Jul 12 06:10:21 2021
    On Tuesday, January 3, 2006 at 12:16:34 PM UTC-6, NV wrote:
    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

    When doing multiple table updates, I like to run the edits in a transaction. The following code can be used in a form's button click event. Replacing "ABC" and "2006" with a field reference. Change the query by replacing YourTable, [CodeCol] and [YearCol]
    with the proper table and column names. This code will add 4 rows or none.

    Private Sub btnAdd4_Click()
    on error goto errbtnAdd4_Click
    dim transactionActive as boolean
    dim add4QueryDef as DAO.QueryDef
    transactionActive = false
    set add4QueryDef = currentdb.CreateQueryDef(vbnullstring, "PARAMETERS [InsertCode] Text ( 255 ), [InsertYear] Text ( 255 ); INSERT INTO yourTable( [CodeCol], [YearCol]) VALUES([InsertCode], [InsertYear]);
    add4QueryDef.Paramiters(0) = "ABC"
    add4QueryDef.Paramiters(1) = "2006"
    DBEngine.Workspaces(0).BeginTrans
    transactionActive = true
    add4QueryDef .Execute dbFailOnError + dbSeeChanges
    add4QueryDef .Execute dbFailOnError + dbSeeChanges
    add4QueryDef .Execute dbFailOnError + dbSeeChanges
    add4QueryDef .Execute dbFailOnError + dbSeeChanges
    donebtnAdd4_Click:
    on error resume next
    if transactionActive then
    DBEngine.Workspaces(0).CommitTrans
    endif
    set add4QueryDef = Nothing
    exit sub
    errbtnAdd4_Click:
    debug.print err.Description
    if transactionActive then
    on error resume next
    DBEngine.Workspaces(0).Rollback
    endif
    transactionActive = false
    Resume donebtnAdd4_Click
    end Sub

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Ron Paii@21:1/5 to All on Mon Jul 12 13:31:14 2021
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)