Connection/Recordset

[turfbult]

Hello all,

I am not quite sure how to use the recordset....

I set up a database (test) with a table (client) in MS Access. I now want to enter details from a form and update the client table with those details. I bind my objects on the form to the tablefields and then want to use something like rsrecord.addNew or rsRecord.Update, BUT my recordset must first be opened and this is where my problem is!!! To open my recordset I'll do something like..
rsRecord.Open ("select * from client")
Now I can use the rsRecord.AddNew and rsRecord.Update etc, but this cannot be right because it will take so long to select * from client (especially if it is a huge table) that NO user will want to use it.

My questions..
1.) How do you open a recordset without having to waste time etc by selecting * from client or for that matter only selecting ONE record from client??

2.) Are my steps right... rsrecord.open(select * from client) and the I can use the other rsrecord.?? features??

3.) Do I only open and close the recordset ONCE or each time I want to use it??

PLEASE help as this has been troubling me since I started using VB (admittedly not too long ago!!!)

Thanks.

[Gary.Lowe]

You could try useing the INSERT SQL i.e


dim cnn as ADOB.Connection
Set cnn = New ADODB.Connection

'open the connection to the database
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "C:\MyDB"
End With

'Create the sql to insert into the fields in the client table
cnn.Execute " INSERT INTO Client " _
& "(FirstName,LastName, Title) VALUES " _
& "('turf', 'bult', 'Developer');"



This should enable you to add the record without creating a recordset

[turfbult]

Gary,

This is what I have been doing, but it gets a bit hectic when you have a form with many objects!! The databinding then doesn't help either, does it.

Is using sql rather than the recordset better or doesn't this really matter??

[theroper]

O.k. Don`t know if this will help..

You dont need to do a select statment to open a recordset.
Just pass in the table name..Look at VB`s help on the open command

You could also just select the first record

using the TOP command in your sql query

i.e. "SELECT TOP 1 * from ----"

The recordset only needs to be opened and closed once within the session