Re: Smartest way toa add records manually

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

  • Re: Inline SQL vs stored procs on SQL Server 7 and 2000
    ... Probably few of us know how it was done in ADO. ... However if the stored procedure is faster than a dynamic procedure in ADONET ... applications a couple years ago were the SQL statements were inline. ... were variables in the code I used SQL parameters in the stored procs. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Sql / Dot Net General Discussion
    ... My primary experience is developing applications using VB or DotNet. ... have some sql skills but they are limited. ... We had very limited DTS's wrtten and stored procs were ... apps that call other stored procs, that call others, etc. ...
    (microsoft.public.dotnet.general)
  • Re: Application, database and schema?
    ... > hardware interface for the monitoring. ... This subsystem understands how to talk to the ... > this case an RDB with a SQL interface. ...
    (comp.object)
  • Re: Using parameters with the low level ADO API, trying to avoid the memory leaks.
    ... MS ADO passes SQL directly to OLEDB provider. ...
    (borland.public.delphi.database.ado)
  • Re: Programmatically changing a SQL view in a ADP
    ... One of the differences between DAO and ADO is that DAO combines data ... there isn't a way to do this successfully with ADOX. ... But T-SQL, SQL Server's ... Dim cn As ADODB.Connection ...
    (microsoft.public.access.modulesdaovba)