[dba-SQLServer]RE: Convert VBA to SQL pointers

David Emerson davide at dalyn.co.nz
Thu Oct 2 14:18:37 CDT 2003


Thanks David.  I think the key word is cursor.  This seems to be the SQL 
equivalent of a recordset.  I will investigate this further.

David

At 2/10/2003, you wrote:

>I would say yes, definitely.  There are a few tools in sqlserver that will
>help you, but I didn't read through your code enough to give any concrete
>proposals.  You can avoid moving any data over the wire to the front end by
>using stored procedures.  I would modularize the code here to have one
>calling procedure that perhaps uses a cursor or table udf to identify the
>records you want to act on, then call sub procedures to insert the records
>you want, passing appropriate parameters.  You could have one sub procedure
>per insert, thereby making your code a bit easier to manage.
>
>You could also look at triggers to see if they can be any use here.  I am
>not super experienced, but I have read that triggers are sort of old-school
>db technology, and may be on their way out, so you might not want to go down
>that road.  I have seen from my own experience that triggers are trickier to
>get right.
>
>At any rate, I don't think any of the code you posted needs to be done on
>the front end.
>
>hth.  D. Lewis
>
>
> > Message: 2
> > Date: Thu, 02 Oct 2003 10:39:17 +1200
> > From: David Emerson <davide at dalyn.co.nz>
> > Subject: [dba-SQLServer]Convert VBA to SQL pointers
> > To: dba-SQLServer at databaseadvisors.com
> > Message-ID: <5.2.0.9.0.20031002102433.00b1add0 at mail.dalyn.co.nz>
> > Content-Type: text/plain; format=flowed; charset=us-ascii
> >
> > Group,
> >
> > AXP FE, SQL2000 BE.
> >
> > I am looking for pointers at this stage as how I can do the
> > task.  I have a
> > vba procedure which runs on the click of a button (most of it
> > is replicated
> > below).  As it is currently dealing with over 2000 meters it
> > takes a while
> > to run (because it is going back and forth between Access and SQL).
> >
> > Basically what it does is loop through one recordset of
> > meters, looks up
> > values from other related tables, then adds records to a
> > couple of other
> > tables.  It is things like looping through a recordset, and
> > checking that
> > records don't exist before creating new records that I am unsure of.
> >
> > Is there a way to write the same thing in SQL so that it can
> > all be run in
> > the BE?
> >
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com



More information about the dba-SQLServer mailing list