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

Nicholson, Karen knicholson at gpsx.net
Thu Oct 2 15:29:44 CDT 2003


I posted a cursor question on Monday or Tuesday.  I have since studied the
world of cursors.  They should not be used, unless in cases such as these
where you need to move through each row like we did in the old days.  I am
almost done with my cursor hell, and think by tomorrow I will have it
working.  I have my code documented pretty well, so I will e it to you when
I am done.

-----Original Message-----
From: David Emerson [mailto:davide at dalyn.co.nz]
Sent: Thursday, October 02, 2003 3:19 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer]RE: Convert VBA to SQL pointers


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

_______________________________________________
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