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

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?
>