Other Topics > Smart Access 1996-2006 > Jun-2002 > ADO and Subform Performance

ADO and Subform Performance

<< Click to Display Table of Contents >>

Navigation:  Other Topics > Smart Access 1996-2006 > Jun-2002 >

ADO and Subform Performance

 

Peter Vogel

 

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

are related).

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

for you.

 

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:

ADO and Subform Performance