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" <do**********@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

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

[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).

Terry Kreft wrote:

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

[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" <no****@nospam.nospam> wrote in message
news:yp******************************@comcast.com. ..

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).

Terry Kreft wrote:

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

[Peter Sutton]

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

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

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