Add data without creating a large recordset

[playared]

Is there a way to add new records to an Access database (using ADO) without selecting all the records with the query statement?

I have a database with very large data elements and using something like the following is extremely slow:

With adoPlies
.ConnectionString = connect
.RecordSource = "Select * from Ply"
End With
adoPlies.Refresh
adoPlies.Recordset.MoveLast

adoPlies.Recordset.AddNew

….code for data to add …..

adoPlies.Recordset.Update

I would like to add new data without creating a recordset of all the data in the table.

[erickwidya]

from what i know there's 3 ways u can do it (through INSERT sql)
sSQL = "INSERT INTO tbl1(field1, field2) VALUES('" & txt1.text & "','" & txt2.text & "')"
1. via Connection object
ex : Conn.execute sSQL,,adcmdText
2. via Command Object
ex : cmd.commandtext = sSQL
cmd.activeconnection = Conn
cmd.execute
3. via Recordset Object
ex : rs.open sSQL, Conn,adOpenDynamic, adLockOptimistic,adcmdText

but in ur case it's better use Connection
Read more about it at MSDN

regards

[Krzemo]

IMHO this is not needed:

adoPlies.Refresh
adoPlies.Recordset.MoveLast Removing this should speed up original code.

U can open table instead of select statement too.

But of course I would prefer using commands for inserts/updates like erickwidya - but with parametrized query.

Best regards,
Krzemo.

[Shuja Ali]

But of course I would prefer using commands for inserts/updates like erickwidya - but with parametrized query.
My full points to Krezmo.. the best way to update/insert data in to any database is to use a Parameterized query.. this way u save a lot of time and u don't have to take care of special characters, which might create problem when u build a query on the Fly..

So it is always better to use Command object to insert/update the records, it also gives a bit of security to the application..

[erickwidya]

...- but with parametrized query. yes..but it really awful if u have to define PQ for each table u wish to update/insert like define how many fields, values, which table..or there's other way using PQ?? :confused:

...this way u save a lot of time and u don't have to take care of special characters... :) u got that right vb_the_best, i just realize it now.