By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,446 Members | 2,998 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,446 IT Pros & Developers. It's quick & easy.

Error: This recordset is not updateable

P: n/a
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.ConnectionString = "DSN=test;uid=admin;pwd="
'Open the connection
cntConn1.Open

'Specify the SQL statement
cmd.ActiveConnection = cntConn1
cmd.CommandText = "SELECT LName FROM tblMemberInfo"
'Open the recordset
rstMember.CursorLocation = adUseClient
rstMember.Open cmd, , adOpenStatic, adLockBatchOptimistic, adCmdText
Me.UniqueTable = "tblMemberInfo"

Set Me.Recordset = rstMember
For Each fld In rstMember.Fields
For Each cntl In Me.Controls
If cntl.Name = fld.Name Then
cntl.ControlSource = fld.Name
End If
Next
Next

End Sub
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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.ConnectionString = "DSN=test;uid=admin;pwd="
'Open the connection
cntConn1.Open

'Specify the SQL statement
cmd.ActiveConnection = cntConn1
cmd.CommandText = "SELECT LName FROM tblMemberInfo"
'Open the recordset
rstMember.CursorLocation = adUseClient
rstMember.Open cmd, , adOpenStatic, adLockBatchOptimistic, adCmdText
Me.UniqueTable = "tblMemberInfo"

Set Me.Recordset = rstMember
For Each fld In rstMember.Fields
For Each cntl In Me.Controls
If cntl.Name = fld.Name Then
cntl.ControlSource = fld.Name
End If
Next
Next

End Sub


Nov 12 '05 #2

P: n/a
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.ConnectionString = "DSN=test;uid=admin;pwd="
'Open the connection
cntConn1.Open

'Specify the SQL statement
cmd.ActiveConnection = cntConn1
cmd.CommandText = "SELECT LName FROM tblMemberInfo"
'Open the recordset
rstMember.CursorLocation = adUseClient
rstMember.Open cmd, , adOpenStatic, adLockBatchOptimistic, adCmdText
Me.UniqueTable = "tblMemberInfo"

Set Me.Recordset = rstMember
For Each fld In rstMember.Fields
For Each cntl In Me.Controls
If cntl.Name = fld.Name Then
cntl.ControlSource = fld.Name
End If
Next
Next

End Sub


Nov 12 '05 #3

P: n/a
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.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 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.ConnectionString = "DSN=test;uid=admin;pwd="
'Open the connection
cntConn1.Open

'Specify the SQL statement
cmd.ActiveConnection = cntConn1
cmd.CommandText = "SELECT LName FROM tblMemberInfo"
'Open the recordset
rstMember.CursorLocation = adUseClient
rstMember.Open cmd, , adOpenStatic, adLockBatchOptimistic, adCmdText
Me.UniqueTable = "tblMemberInfo"

Set Me.Recordset = rstMember
For Each fld In rstMember.Fields
For Each cntl In Me.Controls
If cntl.Name = fld.Name Then
cntl.ControlSource = fld.Name
End If
Next
Next

End Sub

Nov 12 '05 #4

P: n/a
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
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.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.ConnectionString = "DSN=test;uid=admin;pwd="
>'Open the connection
> cntConn1.Open
>
>'Specify the SQL statement
> cmd.ActiveConnection = cntConn1
> cmd.CommandText = "SELECT LName FROM tblMemberInfo"
>
>
>'Open the recordset
> rstMember.CursorLocation = adUseClient
> rstMember.Open cmd, , adOpenStatic, adLockBatchOptimistic, adCmdText
> Me.UniqueTable = "tblMemberInfo"
>
> Set Me.Recordset = rstMember
> For Each fld In rstMember.Fields
> For Each cntl In Me.Controls
> If cntl.Name = fld.Name Then
> cntl.ControlSource = fld.Name
> End If
> Next
> Next
>
>End Sub
>


Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.