using adodb to update mysql db from ms access

[bob lambert]

Arghh

Trying to migrate from all access to access front end and mysql backend. Simple open and edit recordset is now beyond my comprehension.

How do I open a recordset (query based) and make a change, then update the underlying table. I used to do this in access

dim mdb as database
dim qd as querydef
dim rs as recordset

set mdb=currentdb()
Set qd = mdb.QueryDefs("qry2")
qd.Parameters("[Forms]![frmRules]![ID]") = Forms!frmRules!ID
Set rs = qd.OpenRecordset
rs.Edit
rs!Date="January 1, 1980"
rs.Update

Now using myodbc and adodb to connect to mysql, I do this:

Dim adoCon As ADODB.Connection
Dim adoCmd As ADODB.Command
Dim rs As ADODB.Recordset

Set adoCon = New ADODB.Connection
adoCon.CursorLocation = adUseClient
adoCon.Open "driver=" & _
"{MySQL ODBC 3.51 Driver};database=mydb;server=192.168.0.3;user=mydb_user;password=123456789;"
If adoCon.Errors.Count > 0 Then
MsgBox "Could not open ado connection"
GoTo LastLine
End If
Set adoCmd = New ADODB.Command
With adoCmd
.ActiveConnection = CurrentProject.Connection
.Properties("Jet OLEDB:Stored Query") = True
.CommandText = "qry2"
' did not need these
'.Parameters("[Forms]![frmRules]![ID]").Type = adInteger
'.Parameters("[Forms]![frmRules]![ID]").size = 10
.Parameters("[Forms]![frmRules]![ID]") = Forms!frmRules!ID
Set rs = .Execute
End With

' this does not work cuz no .edit capability !!!
' can only use .addnew
rs.Edit
rs!Date="January 1, 1980"
' this does not work either
rs.Update



Even if I change to server side its not apparent that I can use .edit. In searching internet I did see some mention about editing the table directly and not a query based recordset of the table. Is that what I need ??? Did not think I could use client side then.

Thanks for help

Bob