Recordsets that can be edited (MySQL ODBC)

[DJJ]

Does anyone know how to create a recordset using MS Access 2003, ADO & MySQL
ODBC that can be edited? The following code only returns a recordset that
cannot be edited



Private Sub Form_Open(Cance l As Integer)

Dim cn As ADODB.Connectio n

Dim rs As ADODB.Recordset



Set cn = New ADODB.Connectio n



With cn

.Provider = "MSDASQL.1"

.Properties("Pe rsist Security Info").Value = "False"

.Properties("Da ta Source").Value = "MyDatabase "

.Open

End With



Set rs = New ADODB.Recordset

With rs

Set .ActiveConnecti on = cn

.Source = "SELECT * FROM inventory"

.LockType = adLockOptimisti c

.CursorType = adOpenStatic

.CursorLocation = adUseClient

.Open



End With



Set Me.Recordset = rs

Set rs = Nothing

Set cn = Nothing



End Sub

[Thomas Bartkus]

Re: Recordsets that can be edited (MySQL ODBC)

cn.Mode = adModeReadWrite

AND

rs.CursorType = adOpenDynamic

OR (maybe! I don't use ADO much.)
rs.CursorType = adOpenKeyset

You can't use the adOpenForwardOn ly cursor type if you want to write to the
recordset.

Thomas Bartkus

Incidentally - your 2 way R/W recordset is bound to be slower. I would open
up a separate write channel myself.

"DJJ" <gemdjj@writme. com> wrote in message
news:W5nqe.3450 $jS1.1652@newss vr17.news.prodi gy.com...[color=blue]
> Does anyone know how to create a recordset using MS Access 2003, ADO &[/color]
MySQL[color=blue]
> ODBC that can be edited? The following code only returns a recordset that
> cannot be edited
>
>
>
> Private Sub Form_Open(Cance l As Integer)
>
> Dim cn As ADODB.Connectio n
>
> Dim rs As ADODB.Recordset
>
>
>
> Set cn = New ADODB.Connectio n
>
>
>
> With cn
>
> .Provider = "MSDASQL.1"
>
> .Properties("Pe rsist Security Info").Value = "False"
>
> .Properties("Da ta Source").Value = "MyDatabase "
>
> .Open
>
> End With
>
>
>
> Set rs = New ADODB.Recordset
>
> With rs
>
> Set .ActiveConnecti on = cn
>
> .Source = "SELECT * FROM inventory"
>
> .LockType = adLockOptimisti c
>
> .CursorType = adOpenStatic
>
> .CursorLocation = adUseClient
>
> .Open
>
>
>
> End With
>
>
>
> Set Me.Recordset = rs
>
> Set rs = Nothing
>
> Set cn = Nothing
>
>
>
> End Sub
>
>[/color]

[DJJ]

Re: Recordsets that can be edited (MySQL ODBC)

I tried that and I also selected 'allow dynamic cursors' in the MySQL DSN
but Access still presents the data as non updateable.

DJJ

"Thomas Bartkus" <thomasbartkus@ comcast.net> wrote in message
news:eq2dncOxSY KEDDDfRVn-sA@telcove.net. ..[color=blue]
> cn.Mode = adModeReadWrite
>
> AND
>
> rs.CursorType = adOpenDynamic
>
> OR (maybe! I don't use ADO much.)
> rs.CursorType = adOpenKeyset
>
> You can't use the adOpenForwardOn ly cursor type if you want to write to[/color]
the[color=blue]
> recordset.
>
> Thomas Bartkus
>
> Incidentally - your 2 way R/W recordset is bound to be slower. I would[/color]
open[color=blue]
> up a separate write channel myself.
>
> "DJJ" <gemdjj@writme. com> wrote in message
> news:W5nqe.3450 $jS1.1652@newss vr17.news.prodi gy.com...[color=green]
> > Does anyone know how to create a recordset using MS Access 2003, ADO &[/color]
> MySQL[color=green]
> > ODBC that can be edited? The following code only returns a recordset[/color][/color]
that[color=blue][color=green]
> > cannot be edited
> >
> >
> >
> > Private Sub Form_Open(Cance l As Integer)
> >
> > Dim cn As ADODB.Connectio n
> >
> > Dim rs As ADODB.Recordset
> >
> >
> >
> > Set cn = New ADODB.Connectio n
> >
> >
> >
> > With cn
> >
> > .Provider = "MSDASQL.1"
> >
> > .Properties("Pe rsist Security Info").Value = "False"
> >
> > .Properties("Da ta Source").Value = "MyDatabase "
> >
> > .Open
> >
> > End With
> >
> >
> >
> > Set rs = New ADODB.Recordset
> >
> > With rs
> >
> > Set .ActiveConnecti on = cn
> >
> > .Source = "SELECT * FROM inventory"
> >
> > .LockType = adLockOptimisti c
> >
> > .CursorType = adOpenStatic
> >
> > .CursorLocation = adUseClient
> >
> > .Open
> >
> >
> >
> > End With
> >
> >
> >
> > Set Me.Recordset = rs
> >
> > Set rs = Nothing
> >
> > Set cn = Nothing
> >
> >
> >
> > End Sub
> >
> >[/color]
>
>[/color]