Quickly Add 10000 records to Access 2000

I am currently developing a VB6 program where, at one point, I need to add 10000 records to a table. Right now what I am doing is basically:

dim rs as new adodb.recordset

rs.Open "select * from TABLE1", sConnection, adOpenKeyset, adLockOptimistic

For i = 1 to 10000
   rs(0).value = "blah"
   rs(1).value = "blah blah"
Next i

This method is proving to be very very slow. Is there any other way to do this? Maybe some kind of batch import? Any suggestions/ideas will be welcome.
Who is Participating?
kdg2000Connect With a Mentor Commented:
The batch addition of records is possible.
Approximately so:

Dim rstTitles As ADODB.Recordset
  Set rstTitles = New ADODB.Recordset
   rstTitles.CursorType = adOpenKeyset
   rstTitles.LockType = adLockBatchOptimistic
   rstTitles.Open "select * from TABLE1", sConnection, , , adCmdTable
   Do Until rstTitles.EOF
        rstTitles(0).Value = "blah"
        rstTitles(1).Value = "blah blah"
   If MsgBox("Save all changes?", vbYesNo) = vbYes Then
   End If

End Sub
Ryan ChongCommented:

try rs.execute ("....")
Get Cisco Certified in IT Security

There’s a high demand for IT security experts and network administrators who can safeguard the data that individuals, corporations, and governments rely on every day. Pursue your B.S. in Network Operations and Security and gain the credentials you need for this high-growth field.

of course : adodb.execute
try this

Private Sub Command1_Click()
Dim rs As New ADODB.Recordset
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TestDb.mdb;Persist Security Info=False"
rs.Open "select * from TABLE1", sConnection, adOpenKeyset, adLockOptimistic

x = Timer
For i = 1 To 10000
  rs.AddNew Array("field1", "field2"), Array("blah", "blah blah")
Next i

MsgBox (Timer - x)
End Sub
seeing your code it seems duplicate values is not a problem. in that cae you can try the following.

add one record manually
then run the following code

dim intCount as integer
dim strSQL as string
for intCount = 1 to 12
    strSQL = "insert into Table1 Select * from Table1"
    sconnection.execute strSQL
next intCount

by the time u comeout of for loop you should have about 16000 records in your table.

ibrahim-darahem, you are relatively new to this site so I would ask you to please read the guidelines on comments vs answers at the bottom of this page and on http://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp 

It is not considered good practice to lock questions in this way, as this places these questions in the locked section and reduces the visibility of the question to other experts. It also tends to put pressure on the questioner to accept the proposed answer. In future, please do not post "answers" except in trivial questions which have one and only one answer such as what is 1+1. I know that is a bit simplistic but I am sure you get the idea.

If you continue to post answers, you will a) get some far more severe reactions from less tolerant experts, b) run the risk of your account being referred to customer services who may consider suspending your account.

Having said that I hope that you continue to contribute to this site and that your experience proves useful to many others in future.
Valliappan ANSenior Tech ConsultantCommented:
Try this:

for i = 1 to 10000
  sConnection.Execute "INSERT INTO Table1(field1,field2) VALUES('blah','blah   blah')"

'If you want the no. i, to be used to identify the records, you can use it in the field values, here. Note that SQL queries execution is faster than recordsets usage.
Rejecting ibrahim-darahem's answer, and transforming it back to a comment, for above reasons.

This does not mean this comment is not right, you can still accept it.

Community Support MOderator @ Experts-Exchange

The following is a list of your open questions. You continue to log in to EE, but do not respond.

Please do not give grades less than A without specifically stating why.

Please do not use the delete button, it doesn't work. You must state your intent to delete the question IN the question. After a full three days has passed and no one has objected to deleting the question, you must post a question at community support (CS) at:


You can title the question something like "Please delete this question." Provide a link to the question in the question at CS.

You have never provided feedback:

You asked for further explanation, then never gave feedback to it:

I am sending a copy of my post to the Community Support Administrator; you should hear from him in a day or two. I am also posting in all of your open questions.

thanks in advance for cleaning them up!
community support moderator
Hi dougtimms,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept kdg2000's comment(s) as an answer.

dougtimms, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
DanRollins -- EE database cleanup volunteer
Per recommendation, force-accepted.

CS Moderator
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.