Error: This recordset is not updateable

[MNC]

I'm using Access2002, and can't seem to get an updateable recordset going
:-(

What am I doing wrong, here's the code. The form's controls are not locked,
the recordset type is Dynaset (changing to Dynaset inconsistent updates does
not work), I'm allowing edits, and I'm at a loss ...

Please help!

Option Compare Database
Public rstMember As New Recordset
Public cntConn1 As New Connection
Public cmd As New Command


Private Sub Form_Close()
rstMember.Close
cntConn1.Close

Set rstMember = Nothing
Set cntConn1 = Nothing
Set cmd = Nothing

End Sub

Private Sub Form_Load()

Dim i As Integer
Dim cntl As Control
Dim fld As Field


'Specify the connect string
cntConn1.Connec tionString = "DSN=test;uid=a dmin;pwd="
'Open the connection
cntConn1.Open

'Specify the SQL statement
cmd.ActiveConne ction = cntConn1
cmd.CommandText = "SELECT LName FROM tblMemberInfo"


'Open the recordset
rstMember.Curso rLocation = adUseClient
rstMember.Open cmd, , adOpenStatic, adLockBatchOpti mistic, adCmdText
Me.UniqueTable = "tblMemberI nfo"

Set Me.Recordset = rstMember
For Each fld In rstMember.Field s
For Each cntl In Me.Controls
If cntl.Name = fld.Name Then
cntl.ControlSou rce = fld.Name
End If
Next
Next

End Sub

[Tom van Stiphout]

Re: Error: This recordset is not updateable

On Fri, 23 Jan 2004 22:47:04 GMT, "MNC" <HTGuy1@rogers. com> wrote:

Perhaps the table doesn't have a Primary Key?
-Tom.

[color=blue]
>I'm using Access2002, and can't seem to get an updateable recordset going
>:-(
>
>What am I doing wrong, here's the code. The form's controls are not locked,
>the recordset type is Dynaset (changing to Dynaset inconsistent updates does
>not work), I'm allowing edits, and I'm at a loss ...
>
>Please help!
>
>Option Compare Database
>Public rstMember As New Recordset
>Public cntConn1 As New Connection
>Public cmd As New Command
>
>
>Private Sub Form_Close()
> rstMember.Close
> cntConn1.Close
>
> Set rstMember = Nothing
> Set cntConn1 = Nothing
> Set cmd = Nothing
>
>End Sub
>
>Private Sub Form_Load()
>
>Dim i As Integer
>Dim cntl As Control
>Dim fld As Field
>
>
>'Specify the connect string
> cntConn1.Connec tionString = "DSN=test;uid=a dmin;pwd="
>'Open the connection
> cntConn1.Open
>
>'Specify the SQL statement
> cmd.ActiveConne ction = cntConn1
> cmd.CommandText = "SELECT LName FROM tblMemberInfo"
>
>
>'Open the recordset
> rstMember.Curso rLocation = adUseClient
> rstMember.Open cmd, , adOpenStatic, adLockBatchOpti mistic, adCmdText
> Me.UniqueTable = "tblMemberI nfo"
>
> Set Me.Recordset = rstMember
> For Each fld In rstMember.Field s
> For Each cntl In Me.Controls
> If cntl.Name = fld.Name Then
> cntl.ControlSou rce = fld.Name
> End If
> Next
> Next
>
>End Sub
>[/color]

[Steve Jorgensen]

Re: Error: This recordset is not updateable

Access will allow you to bind a recordset to a form at run-time, but it's not
very happy unless the recordset was opened using using the standard Access ADO
driver, either directly or as a wrapper around the SQL Server provider. It
looks to me like you are using the ODBC provider, and Access form's (as of
Access 2002) don't deal with that where editing is concerned.

Now, for the next problem. Access does not play well with BatchOptimistic ADO
recordsets. It will let you "edit" them, but somehow, Access reaches
underneath the recordset and writes directly to the underlying values instead
of the updated values. This is completely broken behavior because, not only
has the value change -not- been recorded, but the supposedly pristine record
of the original state has been overwritten, so it appears that the back-end
data has been changed by another user since the edit began.

For all I know, this may all have changed in Access 2003, but I haven't heard
one way or the other. ADO in Access doesn't seem to have caught on enough for
there to be many folks out there reporting on what has or hasn't improved in
this regard, and I haven't gotten around to checking it out myself.

On Fri, 23 Jan 2004 22:47:04 GMT, "MNC" <HTGuy1@rogers. com> wrote:
[color=blue]
>I'm using Access2002, and can't seem to get an updateable recordset going
>:-(
>
>What am I doing wrong, here's the code. The form's controls are not locked,
>the recordset type is Dynaset (changing to Dynaset inconsistent updates does
>not work), I'm allowing edits, and I'm at a loss ...
>
>Please help!
>
>Option Compare Database
>Public rstMember As New Recordset
>Public cntConn1 As New Connection
>Public cmd As New Command
>
>
>Private Sub Form_Close()
> rstMember.Close
> cntConn1.Close
>
> Set rstMember = Nothing
> Set cntConn1 = Nothing
> Set cmd = Nothing
>
>End Sub
>
>Private Sub Form_Load()
>
>Dim i As Integer
>Dim cntl As Control
>Dim fld As Field
>
>
>'Specify the connect string
> cntConn1.Connec tionString = "DSN=test;uid=a dmin;pwd="
>'Open the connection
> cntConn1.Open
>
>'Specify the SQL statement
> cmd.ActiveConne ction = cntConn1
> cmd.CommandText = "SELECT LName FROM tblMemberInfo"
>
>
>'Open the recordset
> rstMember.Curso rLocation = adUseClient
> rstMember.Open cmd, , adOpenStatic, adLockBatchOpti mistic, adCmdText
> Me.UniqueTable = "tblMemberI nfo"
>
> Set Me.Recordset = rstMember
> For Each fld In rstMember.Field s
> For Each cntl In Me.Controls
> If cntl.Name = fld.Name Then
> cntl.ControlSou rce = fld.Name
> End If
> Next
> Next
>
>End Sub
>[/color]

[MNC]

Re: Error: This recordset is not updateable

Thank you for the detailed explanation. I can easily remove the
BatchOptimistic as I don't really need that: it just slipped in as part of
me trying to get this going.

As for the first, you are in fact correct: I am connecting using the MS
ODBC Driver for Access. I'm learning how to do this by applying general
principles I learned over the years, and the help file: all the references I
find pretty much assume the use of some ODBC driver or another. Where do I
find/install the Access ADO driver?

"Steve Jorgensen" <nospam@nospam. nospam> wrote in message
news:ls0410d2qs gd4al9jj550lvj3 vtrn03rnf@4ax.c om...[color=blue]
> Access will allow you to bind a recordset to a form at run-time, but it's[/color]
not[color=blue]
> very happy unless the recordset was opened using using the standard Access[/color]
ADO[color=blue]
> driver, either directly or as a wrapper around the SQL Server provider.[/color]
It[color=blue]
> looks to me like you are using the ODBC provider, and Access form's (as of
> Access 2002) don't deal with that where editing is concerned.
>
> Now, for the next problem. Access does not play well with BatchOptimistic[/color]
ADO[color=blue]
> recordsets. It will let you "edit" them, but somehow, Access reaches
> underneath the recordset and writes directly to the underlying values[/color]
instead[color=blue]
> of the updated values. This is completely broken behavior because, not[/color]
only[color=blue]
> has the value change -not- been recorded, but the supposedly pristine[/color]
record[color=blue]
> of the original state has been overwritten, so it appears that the[/color]
back-end[color=blue]
> data has been changed by another user since the edit began.
>
> For all I know, this may all have changed in Access 2003, but I haven't[/color]
heard[color=blue]
> one way or the other. ADO in Access doesn't seem to have caught on enough[/color]
for[color=blue]
> there to be many folks out there reporting on what has or hasn't improved[/color]
in[color=blue]
> this regard, and I haven't gotten around to checking it out myself.
>
> On Fri, 23 Jan 2004 22:47:04 GMT, "MNC" <HTGuy1@rogers. com> wrote:
>[color=green]
> >I'm using Access2002, and can't seem to get an updateable recordset going
> >:-(
> >
> >What am I doing wrong, here's the code. The form's controls are not[/color][/color]
locked,[color=blue][color=green]
> >the recordset type is Dynaset (changing to Dynaset inconsistent updates[/color][/color]
does[color=blue][color=green]
> >not work), I'm allowing edits, and I'm at a loss ...
> >
> >Please help!
> >
> >Option Compare Database
> >Public rstMember As New Recordset
> >Public cntConn1 As New Connection
> >Public cmd As New Command
> >
> >
> >Private Sub Form_Close()
> > rstMember.Close
> > cntConn1.Close
> >
> > Set rstMember = Nothing
> > Set cntConn1 = Nothing
> > Set cmd = Nothing
> >
> >End Sub
> >
> >Private Sub Form_Load()
> >
> >Dim i As Integer
> >Dim cntl As Control
> >Dim fld As Field
> >
> >
> >'Specify the connect string
> > cntConn1.Connec tionString = "DSN=test;uid=a dmin;pwd="
> >'Open the connection
> > cntConn1.Open
> >
> >'Specify the SQL statement
> > cmd.ActiveConne ction = cntConn1
> > cmd.CommandText = "SELECT LName FROM tblMemberInfo"
> >
> >
> >'Open the recordset
> > rstMember.Curso rLocation = adUseClient
> > rstMember.Open cmd, , adOpenStatic, adLockBatchOpti mistic, adCmdText
> > Me.UniqueTable = "tblMemberI nfo"
> >
> > Set Me.Recordset = rstMember
> > For Each fld In rstMember.Field s
> > For Each cntl In Me.Controls
> > If cntl.Name = fld.Name Then
> > cntl.ControlSou rce = fld.Name
> > End If
> > Next
> > Next
> >
> >End Sub
> >[/color]
>[/color]

[Steve Jorgensen]

Re: Error: This recordset is not updateable

Are you working from within Access? If so, for one thing, you've got the
driver - it came with Access. For another thing, you might want to consider
using DAO instead of ADO. I'm not saying it's compelling one way or the
other, but when I'm using an Access front-end to an Access back-end, I
generally stick to DAO because that's what Access is doing natively anyway.
The Recordset property of a normal bound form, for instance, will give you a
DAO recordset.

Next, if you do want an ADO connection to the current database, you need look
no farther than CurrentProject. Connection. You can also look at that to see
the connection string format for connecting to any JET database.

On Sat, 24 Jan 2004 14:44:05 GMT, "MNC" <nospam@nospam. nospam> wrote:
[color=blue]
>Thank you for the detailed explanation. I can easily remove the
>BatchOptimisti c as I don't really need that: it just slipped in as part of
>me trying to get this going.
>
>As for the first, you are in fact correct: I am connecting using the MS
>ODBC Driver for Access. I'm learning how to do this by applying general
>principles I learned over the years, and the help file: all the references I
>find pretty much assume the use of some ODBC driver or another. Where do I
>find/install the Access ADO driver?
>
>"Steve Jorgensen" <nospam@nospam. nospam> wrote in message
>news:ls0410d2q sgd4al9jj550lvj 3vtrn03rnf@4ax. com...[color=green]
>> Access will allow you to bind a recordset to a form at run-time, but it's[/color]
>not[color=green]
>> very happy unless the recordset was opened using using the standard Access[/color]
>ADO[color=green]
>> driver, either directly or as a wrapper around the SQL Server provider.[/color]
>It[color=green]
>> looks to me like you are using the ODBC provider, and Access form's (as of
>> Access 2002) don't deal with that where editing is concerned.
>>
>> Now, for the next problem. Access does not play well with BatchOptimistic[/color]
>ADO[color=green]
>> recordsets. It will let you "edit" them, but somehow, Access reaches
>> underneath the recordset and writes directly to the underlying values[/color]
>instead[color=green]
>> of the updated values. This is completely broken behavior because, not[/color]
>only[color=green]
>> has the value change -not- been recorded, but the supposedly pristine[/color]
>record[color=green]
>> of the original state has been overwritten, so it appears that the[/color]
>back-end[color=green]
>> data has been changed by another user since the edit began.
>>
>> For all I know, this may all have changed in Access 2003, but I haven't[/color]
>heard[color=green]
>> one way or the other. ADO in Access doesn't seem to have caught on enough[/color]
>for[color=green]
>> there to be many folks out there reporting on what has or hasn't improved[/color]
>in[color=green]
>> this regard, and I haven't gotten around to checking it out myself.
>>
>> On Fri, 23 Jan 2004 22:47:04 GMT, "MNC" <HTGuy1@rogers. com> wrote:
>>[color=darkred]
>> >I'm using Access2002, and can't seem to get an updateable recordset going
>> >:-(
>> >
>> >What am I doing wrong, here's the code. The form's controls are not[/color][/color]
>locked,[color=green][color=darkred]
>> >the recordset type is Dynaset (changing to Dynaset inconsistent updates[/color][/color]
>does[color=green][color=darkred]
>> >not work), I'm allowing edits, and I'm at a loss ...
>> >
>> >Please help!
>> >
>> >Option Compare Database
>> >Public rstMember As New Recordset
>> >Public cntConn1 As New Connection
>> >Public cmd As New Command
>> >
>> >
>> >Private Sub Form_Close()
>> > rstMember.Close
>> > cntConn1.Close
>> >
>> > Set rstMember = Nothing
>> > Set cntConn1 = Nothing
>> > Set cmd = Nothing
>> >
>> >End Sub
>> >
>> >Private Sub Form_Load()
>> >
>> >Dim i As Integer
>> >Dim cntl As Control
>> >Dim fld As Field
>> >
>> >
>> >'Specify the connect string
>> > cntConn1.Connec tionString = "DSN=test;uid=a dmin;pwd="
>> >'Open the connection
>> > cntConn1.Open
>> >
>> >'Specify the SQL statement
>> > cmd.ActiveConne ction = cntConn1
>> > cmd.CommandText = "SELECT LName FROM tblMemberInfo"
>> >
>> >
>> >'Open the recordset
>> > rstMember.Curso rLocation = adUseClient
>> > rstMember.Open cmd, , adOpenStatic, adLockBatchOpti mistic, adCmdText
>> > Me.UniqueTable = "tblMemberI nfo"
>> >
>> > Set Me.Recordset = rstMember
>> > For Each fld In rstMember.Field s
>> > For Each cntl In Me.Controls
>> > If cntl.Name = fld.Name Then
>> > cntl.ControlSou rce = fld.Name
>> > End If
>> > Next
>> > Next
>> >
>> >End Sub
>> >[/color]
>>[/color]
>[/color]