addnew record to recordset - performance issue

[bambo]

Hi,

I have a large ms access table and would like to add new record to it from an unbound form.

Currently this is the code:

set rst= currentdb.openrecordset ("table1")
rst.addnew
rst!id= myID
rst!name= myname
rst.update

Thing is that opening the record set is very slow (again a big table).

any ideas how to improve performance on this?

Thanks!

[si_the_geek]

The quickest way is to not open a recordset (which copies all of the records from disk to memory), instead just add the record(s) directly to the table.

I'm not sure of the DAO methods, but I think this is right:

Dim strSQL as String
  strSQL = "INSERT INTO table1 ([id], [name]) VALUES (" & myID & ",'" & myname & "')"
  currentdb.Execute strSQL

I have guessed at the data types of your fields, so it may need some minor changes.

[bambo]

There are like 30 fields in this table, and already implimented a recordset for updating the record... so implementing two separate functions (one for updating a record, and one for inserting a record) might be too complex to maintain later on.

Any ideas on whats the fastest way to work with recordset?

tHanks

[si_the_geek]

Updating is also much quicker with SQL - with an UPDATE rather than INSERT. For information on how to write one see the Tutorial in the "SQL" section of our Database Development FAQs/Tutorials (at the top of the Database Development forum).

If speed is an issue, using a recordset is the wrong thing to do. Maintaining two strings (which is all that SQL statements are) isn't a big deal, and the rest of the code can almost certainly be shared.