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]

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

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

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

[Steve Jorgensen]

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" <HT****@rogers. com> wrote:

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

[MNC]

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" <no****@nospam. nospam> wrote in message
news:ls******** *************** *********@4ax.c om...

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" <HT****@rogers. com> wrote:

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 doesnot 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

[Steve Jorgensen]

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" <no****@nospam. nospam> wrote:

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" <no****@nospam. nospam> wrote in message
news:ls******* *************** **********@4ax. com...

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" <HT****@rogers. com> wrote:

>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 notlocked, >the recordset type is Dynaset (changing to Dynaset inconsistent updatesdoes >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
>