Add a defined number of records to a table

[NV]

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

[Red]

You could use something like this:

For X = 1 to NumberEntered
Saverecords()
Next X

Private sub Saverecords()
dim rs as recordsource
set rs = currentdb.openrecordset("mytable")
rs.addnew
rs("field1") = me.myfield.value
etc etc etc
rs.update
end sub

[Terry Kreft]

No that's dreadful, you're opening and closing a table recordset X times.
Your objective with any data work should be:-
Get in
Do the work
Get out

... as fast as you can.

Something like:-

Dim rs as dao.recordset
Dim intX as integer

set rs = currentdb.openrecordset("SELECT * FROM mytable WHERE 1=0")

For intX = 1 to NumberEntered
With rs
.addnew
.Fields("field1") = me.mycontrol.value
.update
End With
Next intX
rs.close
set rs = nothing

OR

Dim db as DAO.Database

Set db = currentdb

For intX = 1 to NumberEntered
db.Execute "INSERT INTO mytable(field1) VALUES(" & me.mycontrol.value &
")"
Next intX
set db = nothing

--
Terry Kreft

"Red" <dougla...@gmail.com> wrote in message
news:1136313507.3...@g49g2000cwa.googlegroups.com...

[Steve Jorgensen]

On the other hand, I once had a performance problem using an ADO
recordset to insert 15000 rows into a table, switched to doing
individual INSERT queries in a loop, and dropped the run time from 15
minutes to a mintue and 10 seconds. I was not closing and reopening the
Connection object, however (equivalent to opening/closing the Database
obejct in this example).

[Lyle Fairfield]

Ten thousand new records in 14 seconds.

Sub AddRecords()
Dim r As ADODB.Recordset
Dim z As Long
Debug.Print Now() '2006-01-03 18:31:52
Set r = New ADODB.Recordset
With r
.CursorLocation = adUseClient
.Open _
"SELECT Field1, Field2, Field3 FROM Test WHERE False", _
CurrentProject.Connection, _
adOpenStatic, _
adLockBatchOptimistic
For z = 0 To 9999
r.AddNew Array(0, 1, 2), Array(z, Chr$(z Mod 256), Now())
Next z
.UpdateBatch
End With
Debug.Print Now() ' 2006-01-03 18:32:06
End Sub

[Lyle Fairfield]

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

[NV]

Hello !

Thank you all for your help, but as MS Access is prety new to me I
don't know how to enter those procedures into a form and make it read
the number I enter in a text box and consequently insert the
correspondent number of records.

[Terry Kreft]

As you're aware, that's slightly different though, personally I would go
with the INSERT statements first anyway as a first stab and then worry about
it if I was having speed issues. I tend not to use inserts/updates on
recordsets if I can avoid it. This is helped by the fact that I mainly work
unbound which gives you much greater flexibility in the way you work.

The point of my post (as I'm also aware you realise) was to point out that
the opening of the connection and creating the recordset X times was the
problem I saw not the iterative insert, you can't get away from iterating
the insert.

--
Terry Kreft

"Steve Jorgensen" <nos...@nospam.nospam> wrote in message
news:ypadnbz6d7_omCbe...@comcast.com...

[Peter Sutton]

On 3 Jan 2006 15:36:08 -0800, "Lyle Fairfield" <lylefa...@aim.com>
wrote:

Given the same data is being inserted in each record, a SQL INSERT
can be quicker than using loops where there is some other table with a
large number of records.

eg INSERT INTO DestinationTable ( Period, Code ) Select Top 10000
2006, 'ABC' FROM LargeTable

P

[Jordi Marsà]

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

[Ron Paii]

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

[Ron Paii]

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.