Updating an ADODB record syntax, I'm very close, but missing something. VB6

[Edward_]

I have no errors with this simple code, but it doesn't write the updated field values to the table.


What am I overlooking? I'm moving everything in this project from DAO to ADO, this is my first save edited record procedure.

Dim iSQLStr As String
Dim rsCon As ADODB.Recordset
Set cnCon = New ADODB.Connection
Set rsCon = New ADODB.Recordset
cnCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                         "Data Source=" & GetDataPath
cnCon.Open
  iSQLStr = "SELECT * FROM tblData WHERE patID = '" & cmbID.Text & "' & AND Num = '" & cmbpatNum.Text & "'"
   rsCon.Open iSQLStr, cnCon, adOpenStatic, adLockOptimistic, adCmdText
    rsCon.Fields("Group").Value = cmbGroup.Text
    rsCon.Fields("Label").Value = txtLabel.Text
    rsCon.Fields("Floor").Value = cmbFloor.Text
    rsCon.Fields("Wing").Value = cmbWing.Text
    rsCon.Fields("Mess").Value = cmbMess.Text
    rsCon.Fields("Trans").Value = cmbTrans.Text
  rsCon.Update
MsgBox "Record Successfully updated", vbInformation 
cnCon.Close

Any guidance appreciated.

[ranman256]

Why are you using vb code?...just put it in a query. odbc the table. No code needed.

[CJ_London]

access 2007 (.accdb) uses ACE, not JET - so may be a reason.

Interested in knowing your reasons for moving to ADO from DAO given it is more limited and you have to write your own filter and sort routines for forms and presumably reports.

Also, since this is an update - why not just set the form recordset to the ADO recordset?

Edit - Ah - just realised you are in VB6, not using Access coding, which might have an impact, but the arguments still stand

[Edward_]

I solved it.

My 'On error resume next' was masking an error in my SQL string.
This new syntax corrected things.

The SQL syntax correction solved the SQL error. (Both are numbers) My correct syntax is now.

iSQLStr = "SELECT * FROM tblData WHERE patID = " & cmbID.Text & " AND Num = " & cmbpatNum.Text & ""

Thanks

[June7]

The trailing & "" is not needed.

[CJ_London]

just noticed an interesting difference between (access) vba and vb6.

in vba the .text property is only available for the control which has the focus, in VB6, it appears to be available at any time

[June7]

In VBA probably don't want Text property anyway. I've never used it. Value is the default property of data controls. Don't specify and Value will be used.

iSQLStr = "SELECT * FROM tblData WHERE patID = " & Me.cmbID & " AND Num = " & Me.cmbpatNum