ADO: Smartest way toa add records manually

[Atlas]

Access 2003 (SP1) + .adp project + ADO 2.8 + MS Sql Server 2000

I'm wondering if in a VBA code script it is more efficient & faster adding
records using a cn.execute(--INSERT-STATEMENT) or if it is better to open a
connection and then rs.addnew + rs.update, considering that the latter needs
a SELECT statement before issueing the addnew+update (what if millions
records? Need to fecth only a few just to open the recordset).

Comments appreciated

 

[Alex White MCDBA MCSE]

Pass-through Queries (select statements that are native SQL, understandable
by SQL directly) are nearly always faster, in fact I cannot thing off a
situation where they are not, given you can write statements like

Update TblTest Set myValue = myValue + 1 Where myValue2=2

for SQL Server to process that is quick for ADO to do it within the
recordset method, requires opening a recordset on the client, using cursors
to move forward, make the change, update the record, move to the next
record, eventually close the recordset.

The problems with direct SQL queries are this:-

you have to form the query, make it readable for the SQL server, it is not
going to help you, so you need to understand exactly what you want.

timing problems, due to the fact the front-end has no idea of what you have
passed to SQL, sometimes it returns to the database so fast the front-end
does not know about the changes.

e.g.

currentproject.connection.execute("Delete from TblTest Where Test_ID=1")
me.requery

sometimes the requery comes to quickly after the execute.

But given all I have said, if you are using SQL server to store your data
why not take advantage of it's ability to do some of the work, instead of
the front-end doing everything, Store procedures within SQL Server is in my
view a thing that separates the men from the boys in databases, sending a
small command across the network for lots of local processing by the SQL
server, seems to be a good thing, I totally believe in dumb-ing down the
front-end and making the back-end do the work.

So Pass-through is the way if you are prepared to spend the time getting the
SQL stuff write.

 

[bob]

Access 2003 (SP1) + .adp project + ADO 2.8 + MS Sql Server 2000

I'm wondering if in a VBA code script it is more efficient & faster adding
records using a cn.execute(--INSERT-STATEMENT) or if it is better to open
a connection and then rs.addnew + rs.update, considering that the latter
needs a SELECT statement before issueing the addnew+update (what if
millions records? Need to fecth only a few just to open the recordset).

Comments appreciated

 

[Robert Morley]

Alex makes a number of good points, though there's something to be said for
the simplicity of a one-line "INSERT..." statement.

If you DO decide to use an INSERT statement of this style (which I find
faster than anything else when you're strictly adding new data and don't
care about running a corresponding SELECT statement, or the timing issues
Alex mentioned), you may also want to consider pre-generating a string with
several INSERT statements in a row, separated by CR/LF's.

Personally, I use an admixture, depending on the nature of what I'm doing.
When I'm not doing a blind insert, the best method I've found to-date is to
use a client-side cursor with the adLockBatchOptimistic option, and let ADO
take care of everything after that. Client-side cursors do have a few
limitations, however, like the inability to read back values in identity
fields and calculated fields, if that's a concern. (And if I'm wrong in
that, somebody PLEASE tell me how, cuz I haven't found a way so far.)



Rob

 

[ZRexRider]

So what's your favorite philosophy?

I've always been old-school in that I either developed a class module
or dozens of specific stored procs for Add, Update, Delete to support
all tables (trying to keep SQL from being sprinkled all over my
project). However, ADO and MS Access GUI make it so easy to tie SQL to
objects or do stuff on the fly I find myself starting to get sloppier.
I find it difficult to teach others to "black box" the SQL code because
it looks so tedius.

Heck I didn't even know that you could do what Alex pointed out:

currentproject.connection.exec­ute("Delete from TblTest Where
Test_ID=1")
me.requery

Now I'm really in trouble!

I've spent years developing VB6/Oracle DB interfaces (using Oracle
Objects for Ole instead of ADO) and never had SQL in the User interface
code. But now I find it silly to develop a stored proc to load a list
box.

I guess my question is - do most of you continue to separate the SQL
from the interface or has the power of ADO lured you into blending?

Thanks

 

[Robert Morley]

It's funny you should ask, actually, because I find myself going in the
opposite direction.

My beginnings were entirely based in Access, so I started by always using
the built-in properties, tying data directly to forms, etc. For small to
medium applications this worked well. Then I discovered the concept of
class modules to represent tables, collections, etc., and started isolating
some things from the obvious, easy way to do them in Access, in order to
provide more flexibility. Then I discovered ADO, and started programming
using that instead of DAO (though some days I wondered why).

More recently, we've transitioned from Access as a back end to SQL Server as
a back end. Then I discovered why I transitioned to ADO. All of a sudden,
things like asynchronous commands became a little more logical and useful.
I also discovered the pitfalls of the .Index/.Seek combination. What had
been the fastest way in Access was impossible in SQL Server. Even in other
code where I had used more generic ADO like .Open <tablename>,
..Find/.Filter, I discovered the added pitfalls of opening a full table
against a SQL Server back end.

While I still use Access' ability to tie forms/listboxes directly to queries
and such, for my class modules, I'm definitely going to more of a generic
approach that's easier to maintain across different front-/back-ends. For
example, sprinkled liberally throughout my code, you can now find lines
like:

DataProject.MakeQuery("MyTableOrView","MyFieldList <or blank for
*>","MyField = " & DataProject.MakeServerString(MyString), "MyOrderByField
DESC")

MakeQuery then handles the specific syntax of a generic Select query, while
MakeServerString handles things like converting apostrophes, CR/LF combos,
etc., and then wraps the string in appropriate delimiters for the server (in
SQL Server's case, single quotes). Similarly, when I develop the need, I'll
be adding DataProject.MakeDeleteQuery, DataProject.MakeInsertQuery, and
whatever else seems appropriate.

Since our next step is .NET instead of VB6, I'm not sure where I'll end up
eventually...with its ability to assign class properties directly to
controls, like you, I'm beginning to wonder whether I'm doing a lot of this
for nothing.



Rob

So what's your favorite philosophy?

I've always been old-school in that I either developed a class module
or dozens of specific stored procs for Add, Update, Delete to support
all tables (trying to keep SQL from being sprinkled all over my
project). However, ADO and MS Access GUI make it so easy to tie SQL to
objects or do stuff on the fly I find myself starting to get sloppier.
I find it difficult to teach others to "black box" the SQL code because
it looks so tedius.

Heck I didn't even know that you could do what Alex pointed out:

currentproject.connection.exec­ute("Delete from TblTest Where
Test_ID=1")
me.requery

Now I'm really in trouble!

I've spent years developing VB6/Oracle DB interfaces (using Oracle
Objects for Ole instead of ADO) and never had SQL in the User interface
code. But now I find it silly to develop a stored proc to load a list
box.

I guess my question is - do most of you continue to separate the SQL
from the interface or has the power of ADO lured you into blending?

Thanks

 

[ZRexRider]

Hey Rob,

Thanks for the response. I kinda figured I wasn't the only one working
through different options. I've isolated the SQL builder code as
well. Even though it is very simple to create a recordset in ADO, I
have only one place in the project that actually does that. Everybody
else just passes SQL text to it.

Thanks for the response
Jerry