Can't update a form's ado recordset through odbc

[Francois]

From http://support.microsoft.com/kb/281998/EN-US/ it seems that you
could update a form's ado recordset with access 2002. I am using a mdb
file with the access 2002 format and I still have a "The recordset is
not updatable" message ... What shall I do to have an ADO recordset I
could update ?

I use mysql and Connector/ODBC 3.51.

I tried the following code on AfterUpdate of a text control: sSearch
is a string with the words being searched for.

Dim Cn As New ADODB.Connection
Dim rec As New ADODB.Recordset
Dim strConnection As String, sql as String

strConnection =
"ODBC;DSN=mysql_dokpe_i01_adm;UID= ... ;PWD= ... ;DATABASE=dokpe_i01"
With Cn
.Provider = "MSDASQL"
.Properties("Data Source").Value = strConnection
.Open
End With

sql = "select concat(nofm, '') as nofm, ti, tri, issn, ex, ad,
concat(noed,'') as noed, dom from jrn where match(ti) against(" &
Chr(39) & sSearch & Chr(39) & " in boolean mode)"
rec.CursorLocation = adUseClient
rec.Open sql, Cn, adOpenKeyset, adLockOptimistic

If Not rec.EOF Then

Set Me.Recordset = rec

Else
MsgBox "No data"
End If
End If
Set rec = Nothing
Set Cn = Nothing

 

[Jamie Collins]

could update a form's ado recordset with access 2002. I am using a mdb
file with the access 2002 format and I still have a "The recordset is
not updatable" message ... What shall I do to have an ADO recordset I
could update ?

I use mysql and Connector/ODBC 3.51.

Suggestion: try creating a VIEW in mysql (assuming VIEWs are
supported) using your SQL code and determine within mysql whether the
VIEW is updateable (I suspect the concat stuff means it isn't) or can
be made updateable (e.g. could be done in SQL Server using INSTEAD OF
triggers). If it's not updateable on the mysql side then it will
cannot be updateable on the Access side.

Jamie.

--

 

[Francois]

Suggestion: try creating a VIEW in mysql (assuming VIEWs are
supported) using your SQL code and determine within mysql whether the
VIEW is updateable (I suspect the concat stuff means it isn't) or can
be made updateable (e.g. could be done in SQL Server using INSTEAD OF
triggers). If it's not updateable on the mysql side then it will
cannot be updateable on the Access side.

Jamie.

--

Thanks for the suggestion: the fact is that the recordset *is*
updatable with vba code in access !

The problem is in the form's recordset property. I have seen posts
where people thought of doing update through unbound controls to the
ado recordset with vba.

Francois