Rocksolid Light

Welcome to novaBBS (click a section below)

mail  files  register  newsreader  groups  login

Message-ID:  

Hailing frequencies open, Captain.


devel / comp.databases.ms-access / Re: Add a defined number of records to a table

SubjectAuthor
o Re: Add a defined number of records to a tableJordi Marsà

1
Re: Add a defined number of records to a table

<5a28d69a-8462-4628-b619-92e1a88a5ee9n@googlegroups.com>

 copy mid

https://www.novabbs.com/devel/article-flat.php?id=454&group=comp.databases.ms-access#454

 copy link   Newsgroups: comp.databases.ms-access
X-Received: by 2002:a0c:a223:: with SMTP id f32mr25170138qva.8.1625677683652; Wed, 07 Jul 2021 10:08:03 -0700 (PDT)
X-Received: by 2002:a5b:4c8:: with SMTP id u8mr34266237ybp.255.1625677683395; Wed, 07 Jul 2021 10:08:03 -0700 (PDT)
Path: i2pn2.org!i2pn.org!news.swapon.de!news.uzoreto.com!tr1.eu1.usenetexpress.com!feeder.usenetexpress.com!tr3.iad1.usenetexpress.com!border1.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.databases.ms-access
Date: Wed, 7 Jul 2021 10:08:03 -0700 (PDT)
In-Reply-To: <1136341395.269472.116730@g44g2000cwa.googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=213.195.104.144; posting-account=q8Jf4woAAAB4Tet4rQzI_b5Vtke0vRQI
NNTP-Posting-Host: 213.195.104.144
References: <1136312194.048180.66290@f14g2000cwb.googlegroups.com> <1136331368.473623.308400@g14g2000cwa.googlegroups.com> <1136341395.269472.116730@g44g2000cwa.googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <5a28d69a-8462-4628-b619-92e1a88a5ee9n@googlegroups.com>
Subject: Re: Add a defined number of records to a table
From: jordimar...@gmail.com (Jordi Marsà)
Injection-Date: Wed, 07 Jul 2021 17:08:03 +0000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Lines: 94
 by: Jordi Marsà - Wed, 7 Jul 2021 17:08 UTC

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

1
server_pubkey.txt

rocksolid light 0.9.7
clearnet tor