| Author |
Recordsets that can be edited (MySQL ODBC)
|
|
|
| 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(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
With cn
.Provider = "MSDASQL.1"
.Properties("Persist Security Info").Value = "False"
.Properties("Data Source").Value = "MyDatabase"
.Open
End With
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM inventory"
.LockType = adLockOptimistic
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.Open
End With
Set Me.Recordset = rs
Set rs = Nothing
Set cn = Nothing
End Sub
| |
| Thomas Bartkus 2005-06-13, 9:23 am |
| cn.Mode = adModeReadWrite
AND
rs.CursorType = adOpenDynamic
OR (maybe! I don't use ADO much.)
rs.CursorType = adOpenKeyset
You can't use the adOpenForwardOnly 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@newssvr17.news.prodigy.com...
> 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(Cancel As Integer)
>
> Dim cn As ADODB.Connection
>
> Dim rs As ADODB.Recordset
>
>
>
> Set cn = New ADODB.Connection
>
>
>
> With cn
>
> .Provider = "MSDASQL.1"
>
> .Properties("Persist Security Info").Value = "False"
>
> .Properties("Data Source").Value = "MyDatabase"
>
> .Open
>
> End With
>
>
>
> Set rs = New ADODB.Recordset
>
> With rs
>
> Set .ActiveConnection = cn
>
> .Source = "SELECT * FROM inventory"
>
> .LockType = adLockOptimistic
>
> .CursorType = adOpenStatic
>
> .CursorLocation = adUseClient
>
> .Open
>
>
>
> End With
>
>
>
> Set Me.Recordset = rs
>
> Set rs = Nothing
>
> Set cn = Nothing
>
>
>
> End Sub
>
>
| |
|
| 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@comcas
t.net> wrote in message
news:eq2dncOxSYKEDDD
fRVn-sA@telcove.net...
> cn.Mode = adModeReadWrite
>
> AND
>
> rs.CursorType = adOpenDynamic
>
> OR (maybe! I don't use ADO much.)
> rs.CursorType = adOpenKeyset
>
> You can't use the adOpenForwardOnly 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@newssvr17.news.prodigy.com...
> MySQL
that[color=darkred]
>
>
|
|
|
|