David Emerson
davide at dalyn.co.nz
Thu Oct 2 14:35:02 CDT 2003
Thanks - I would appreciate that. David At 2/10/2003, you wrote: >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 >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com