How to insert a record using VBA?

[smetzger]

I have tried this...

Set myConn = Dim objADOConn As ADODB.Connection
Dim strConn As String
On Error Resume Next
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source="C:\Development\atheer\procurement.mdb"; " & _
"User ID=""; " & _
"Password=""; "
Set objADOConn = CreateObject("ADODB.Connection")

objADOConn.Open strConn
If (Err = 0) And (objADOConn.State = adStateOpen) Then
Set OpenAccessDB = objADOConn
Else
Set OpenAccessDB = Nothing
End If
Set objADOConn = Nothing

Dim strSQL As String
strSQL = "SELECT * from [Order];"

Set myRecordset = CreateObject("ADODB.Recordset")
myRecordset.Open strSQL, myConn, adOpenDynamic, adLockBatchOptimistic
myRecordset.AddNew
myRecordset.Update

txtPoNumber.Caption = myRecordset.Fields("Id")
I get a valid value in my txtPoNumber.Caption object but when I go look at the database there is no new record.

What am I missing?

Thanks,
Scott

p.s. I am using Outlook 2003 and MS Access 2003

[stanl]

You probably need to set the cursortype and locktype properties of the Recordset Object. Look here


http://www.w3schools.com/ado/met_rs_open.asp

Stan

[smetzger]

Isn't that what I have done on the following line...
myRecordset.Open strSQL, myConn, adOpenDynamic, adLockBatchOptimistic

Are those params incorrect? What should they be?

Thanks, Scott

[alimcpill]

as you have specified adLockBatchOptimistic, try calling myRecordset.UpdateBatch

instead of

myRecordset.Update

[smetzger]

That worked. Thanks.

[stanl]

That worked. Thanks.

Sorry I didn't elaborate more. Updatebatch will work for the code you posted, but changing your locktype to plain lockoptimistic [3] might be more reasonable as UpdateBatch is normally used with disconnected recordsets. Also, if you are just performing updates/inserts it might be advisable to open the table directly rather than as SELECT *

just .02 Stan