Re: Smartest way toa add records manually

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

"ZRexRider" <jerryg@xxxxxxx> wrote in message
news:1117276053.382993.311310@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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 wrote:
> 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
>
> "Atlas" <atlaspeak@xxxxxxxxxxx> wrote in message
> news:119e02ig4l9ddbf@xxxxxxxxxxxxxxxxxxxxx
> > 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
> >


.

Relevant Pages