• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

Adding records to MSAccess table slows down

       rstACC.CursorType = adOpenKeyset
        rstACC.LockType = adLockOptimistic
        rstACC.Open "TMP_Fields", cnACC, , , adCmdTable
        rstACC.AddNew
        rstACC!ln = inLN
        rstACC!Field1 = inField1
        rstACC.Update
        Call close_RS(rstACC)

I'm using the above code to add new records to a table in MSAccess db.
As it adds more records (5000+), it appears to slow down.
Am I supposed to be doing something so it doesn't slow down
or am I just imagining it.
Thanks.
0
spoowiz
Asked:
spoowiz
  • 5
  • 3
  • 2
  • +1
2 Solutions
 
ShauliCommented:
   cnACC.Execute "INSERT INTO TMP_Fields " _
                    & "(Field1, Field2,....) VALUES (" _
                    & "'" & inField1& "', " _
                    & "'" & inField2 & "');"

Insert Into is much faster than opening a recordset. You apply the new records directly to the table, using the connection.Execute command, as in the sample above.

S

0
 
PSSUserCommented:
I agreee with Shauli, you should use an insert statement.

The reason your code is slowing down is that each time you open the recordset, you are returning all of the records. However if you want to use a recordset for you insert, instead of the recommended insert statement, then it is best to return an empty recordset to insert into.

Replace:
 rstACC.Open "TMP_Fields", cnACC, , , adCmdTable
with
 rstACC.Open "Select * From TMP_Fields Where 1=2", cnACC, , , adCmdText

1=2 is a deliberately false statement, therefore no records are returned just an empty recordset for appending to.
0
 
EDDYKTCommented:
You should not open connection & recordset everytime when update


ie.

open connection
open recordset as clientcursor


for loop to add new record
next

update back to access
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
spoowizAuthor Commented:
I didn't realize that I was selecting all records each time I was executing rstACC.open.
Thank you. I'll start using either "Insert" or "select .... 1=2" from now.
0
 
spoowizAuthor Commented:
Adding points for split.
0
 
spoowizAuthor Commented:
BTW, which is faster:
INSERT
or
rstACC.Open "Select * From TMP_Fields Where 1=2",
etc?
0
 
ShauliCommented:
Insert is faster by far, as you dont have to open a recordset at all, and you rely on an already open connection.

S
0
 
spoowizAuthor Commented:
FYI,
Cannot use INSERT easily all the time.
If any of the string fields has a single quote ("'"), the .execute will complain.
0
 
PSSUserCommented:
All you need to do is to replace "'" in the string fields with "''" (2 single quotes) by using:
replace(str,"'","''")
0
 
ShauliCommented:
The "Execute is not complaining" - its the database that does. Databases dont like single quotes, for them its either the beginning or the end of a data string. What you can do is replace it with another character, such as the double quote, when you insert or update data, and replace it back to single quote when you read from the database.

S
0
 
spoowizAuthor Commented:
thanks. with pssuser method, i don't have to check or convert with 'every' string field. also has less coding change.
i'm appreciative that i'm aware of these 2 mothods which are much faster than what i was doing.
thanks again.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now