Add new record in ADO

[SparowHawk]

I'm having a rather strange reaction to the following code using VB6. It is an mdb database with the table in question having two fields (It's also empty at present)

SectionID autonumbered (Primary)
Section Text field

Code:

public conn as new ADODB.Connection
public rsSection as new ADODB.Recordset

set conn = new ADODB.Connection
set rsSection = new ADODB.Recordset

With conn
.Provider = "Microsoft.jet.oledb.3.51"
.Open App.Path & "\My.mdb"
End With

rsSection.Open "SELECT * FROM SECTION", conn, adOpenStatic, adLockBatchOptimistic

rsSection.AddNew
rsSection!SECTION = me.txtTextBox.Text
rsSection.Update

rsSection.Close
conn.Close



I have Microsoft ActiveX Data Objects 2.0 Library added to the references.

After running the code and look at the table using Access97, I find it appears blank. However, if I manually insert a letter in the Section (Text) field the autonumber will read 2 instead of the 1 which I would expect. Continually running this code will only add to the autonumbered field but leave the text field blank. Can anyone shed some light on this for me? I'm expreienced in DAO but new to ADO and obviously missing something here ... Could I be opening the database incorrectly?

Thank you!

[Jorge Ramirez]

Try this

dim db as database
dim rs as recordset
'Opens the database Mydb in folder My Documents
set db=opendatabase("C:\My Documents\Mydb.mdb")
'Opens table Mytable from Mydb
set rs=db.openrecordset("Mytable")
'Adds a new record
rs.Addnew
'Copy text from txtMyName to field Section
rs!Section=txtMyName.text
'Update the field
rs.Update
'Close the table
rs.Close
'Close the database
db.Close
'This for clear memory
set db=nothing

[SparowHawk]

Thank you for your reply. This is essentially what I had already done. It turns out that the entire problem was the way I was opening the database. I was using

rsRecordset.Open SQLStatement, connection, adOpenStatic, adLockBatchOptimistic

which caused my problem. The solution was to open the dB with this:

rsRecordset.Open SQLStatement, connection, adOpenStatic, adLockOptimistic

Now all is well. Thanks for the reply though.