Add a defined number of records to a table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

NV - 03 Jan 2006 18:16 GMT

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

Reply to this Message

Red - 03 Jan 2006 18:38 GMT

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

Reply to this Message

Terry Kreft - 03 Jan 2006 22:20 GMT

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

Signature

Terry Kreft

> You could use something like this:
>
[quoted text clipped - 10 lines]
> rs.update
> end sub

Reply to this Message

Steve Jorgensen - 03 Jan 2006 23:15 GMT

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

> No that's dreadful, you're opening and closing a table recordset X times.
> Your objective with any data work should be:-
[quoted text clipped - 32 lines]
> Next intX
> set db = nothing

Reply to this Message

Terry Kreft - 04 Jan 2006 10:28 GMT

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.

Signature

Terry Kreft

> 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
[quoted text clipped - 39 lines]
>> Next intX
>> set db = nothing

Reply to this Message

Lyle Fairfield - 03 Jan 2006 23:36 GMT

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

Reply to this Message

Lyle Fairfield - 04 Jan 2006 02:23 GMT

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

Reply to this Message

NV - 04 Jan 2006 09:04 GMT

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.

Reply to this Message

Peter Sutton - 04 Jan 2006 19:29 GMT

>Ten thousand new records in 14 seconds.
>
[quoted text clipped - 17 lines]
> 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

Reply to this Message