Peter Vogel answers some thorny questions about using
ADO to update a view (you can’t) and setting the
RecordSource property of a subform dynamically to
improve an application’s performance.
I’ m trying to update a recordset, but every time I run the
code, I get the following error: “Run Time Error 3251:
Current recordset doesn’t support updating. This may be
a limitation of the provider, or the selected locktype.”
When I researched this error with Microsoft, they said it
was occurring because I didn’t specify the locktype and
that, by default, a recordset is read-only. I checked, and I did
specify the locktype as adLockPessimistic, but it still doesn’t
work. You should know that my recordset is based on a
command object that calls a stored procedure.
The problem is probably occurring because you’re using
a stored procedure. When you change the value of a field
in a recordset, under the hood ADO generates a SQL
statement to perform the update. That SQL statement is
then sent to whatever database management system
actually owns the table (typically Jet). To figure out what
that update SQL statement should be, ADO looks at the
SQL statement that you used to generate the recordset.
Since all that ADO has to go on in your example is the
name of the stored procedure, ADO can’t figure out how
to generate a SQL statement to do the update.
I’m afraid that you have only two solutions:
• Create another stored procedure (and command
object) that you can use for updates.
• Issue the update SQL yourself.
I recommend the second choice, as the update
statements generated by ADO aren’t very good. For
instance, assume that ADO will create a SQL statement
that updates every field retrieved in the original Select
statement even if you only change one field in the
recordset. This creates the following scenario:
1. You retrieve fields A and B.
2. Another user retrieves fields B and C.
3. You update field A.
4. The other user updates fields B and C (which
5. The other user puts his data back before you.
6. Your update goes through with your new value
for field A and the original value for field B.
The record now has your new value for field A, the
original value for field B, and the other user’s new value
for field C. Not only has the other user lost his change
without notification, but fields B and C (which I said were
related) may be in conflict. Many developers solve this
problem by locking a record when they retrieve it
(pessimistic locking), which reduces an application’s
scalability. However, if each of you had only updated the
fields that you changed, there would be no conflict and
no need for pessimistic locking.
Does ADO update unchanged fields? You don’t know.
And even if you did, can you guarantee that future
versions of ADO won’t change that behavior? If your
application is installed on a system with an older version
of ADO, do you know that previous versions didn’t work
this way? From a performance point of view, it probably
doesn’t make any difference whether you issue the SQL
statement or ADO does, so this is one area where you
shouldn’t give up control.
ADO.NET solves this problem, to a certain extent, by
allowing you to specify the update, insert, and delete
statements for a recordset. You can also get ADO.NET to
generate the statements as ADO does if you don’t want to
do it yourself or are happy with what ADO.NET creates
I’m trying to implement the general approach of passing
SQL strings into forms and reports that you described in
your March 2002 article, “Access Efficiency.” Setting the
RecordSource of my forms to retrieve only the data that I
want has sped up my application. However, I can’t figure out
how to set the RecordSource of a subform. I tried passing a
string into the main form and then setting things like this:
Me.<subformName>.SourceObject = lstOpenArgs
Forms!<subformName>.RecordSource = lstOpenArgs
Me!<subformName>.RecordSource = lstOpenArgs
All gave me different varieties of errors. The subform works
fine when I put a query name in the RecordSource and filter
it, so the problem isn’t with form.
A subform is a kind of object within a form—really just
another kind of control, a control that’s used to hold
forms. The subform control has a bunch of interesting
properties, including one that allows you access to the
form within the subform control. That property is cleverly
Read More Here: