[dba-SQLServer]Convert VBA to SQL pointers

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?

'Create invoice transactions for meters needing estimate readings.
Call basInfo("Creating estimate meter readings.")
Dim rstEstimate As ADODB.Recordset, rst As ADODB.Recordset
Dim rstMeterRead As ADODB.Recordset, rstPlans As ADODB.Recordset
Dim lngCustID As Long, lngMeterID As Long, intInvStatNo As Integer
Dim lngInvStatID As Long, dtmStatDate As Date, lngInvMeterID As Long
Dim sngTotDisc As Single, errMsg As String
Dim bytCount As Byte, sngUnits As Single

Set rstEstimate = basRunDataObject("dbo.spMthEndEstMeterReading", 
adCmdStoredProc) 'List of active meters and estimated monthly readings
Do Until rstEstimate.EOF
     Call basInfo("Creating estimate meter readings." & vbCrLf & 
rstEstimate!SortOrder)
     lngCustID = rstEstimate!CustIDNo
     lngMeterID = rstEstimate!MeterID
     intInvStatNo = DLookup("InvNumber", "dbo.tblCustomers", "CustomerID = 
" & lngCustID)
     lngInvStatID = basCustomDLookUp("StatementID", "dbo.tblCustStatement", 
"CustIDNo = " & lngCustID & " and StatementNumber = " & intInvStatNo)
     If Not IsNull(lngInvStatID) Then 'Statement record exists
             dtmStatDate = basCustomDLookUp("StatementDate", 
"dbo.tblCustStatement", "CustIDNo = " & lngCustID & " and StatementNumber = 
" & intInvStatNo)
     Else 'Should always be a statement - this is a precaution
         MsgBox "There is no current statement for customer " & 
rstEstimate!SortOrder & ".  The record has not been processed.  Please note 
this message and refer to your support officer."
         GoTo EndLoop
     End If
     lngInvStatID = basCustomDLookUp("InvoiceID", "dbo.tblCustInvoice", 
"StatementIDNo = " & lngInvStatID & " and InvoiceNumber = " & intInvStatNo)
     If IsNull(lngInvStatID) Then
         MsgBox "There is no current invoice for customer " & 
rstEstimate!SortOrder & ".  The record has not been processed.  Please note 
this message and refer to your support officer."
         GoTo EndLoop
     End If

     Set rst = basRunDataObject("dbo.tblCustInvoiceMeter", adCmdTable) 
'Check for Meter record
     With rst
         .filter = "MeterIDNo = " & lngMeterID & " and InvoiceIDNo = " & 
lngInvStatID
         If (.EOF Or .BOF) Then 'Meter record not exit - Create meter records
             'Update Meter record
                 rstEstimate!PresMeterReadDate = DateAdd("d", 
-Me!txtEstDays, dtmStatDate)
                 rstEstimate!PresMeterReading = 
rstEstimate!LastMeterReading + int(rstEstimate!MthEstimateUse / 30 * 
(rstEstimate!PresMeterReadDate - rstEstimate!LastMeterReadDate) + 0.5)
                 rstEstimate!ActualEstimate = 2 'Estimate
                 If rstEstimate!NextMeterReadDate <= 
rstEstimate!PresMeterReadDate + Me!txtEstDays Then 'Next read due before 
date of current account
                     rstEstimate!NextMeterReadDate = 
basLastBusDay(DateAdd("m", 1, rstEstimate!NextMeterReadDate - 1)) 'Subtract 
1 to account for 1 Oct date
                 End If
                 'Adjust readings if clock ticked over last month
                 If rstEstimate!DigitsRead > 3 Then
                     If rstEstimate!LastMeterReading >= 10 ^ 
rstEstimate!DigitsRead Then
                         rstEstimate!LastMeterReading = 
rstEstimate!LastMeterReading - 10 ^ rstEstimate!DigitsRead
                         rstEstimate!PresMeterReading = 
rstEstimate!PresMeterReading - 10 ^ rstEstimate!DigitsRead
                     End If
                 End If
             rstEstimate.Update
             Set rstMeterRead = basRunDataObject("dbo.tblMeterReadings", 
adCmdTable)
             With rstMeterRead
                 .AddNew
                     !CustIDNo = lngCustID
                     !MeterIDNo = lngMeterID
                 .Update
             End With
             rstMeterRead.Close
             Set rstMeterRead = Nothing
             .AddNew
                 !InvoiceIDNo = lngInvStatID
                 !MeterIDNo = lngMeterID
                 !LastMeterReadDate = rstEstimate!LastMeterReadDate
                 !CustServPlanIDNo = DLookup("PlanID", 
"dbo.tblCustServicePlans", "Inactive = 0 and MeterIDNo = " & lngMeterID)
             .Update
         End If
     End With
     rst.Close
     Set rst = Nothing

EndLoop:
     rstEstimate.MoveNext
Loop
rstEstimate.Close
Set rstEstimate = Nothing


Regards

David Emerson
DALYN Software Ltd
25b Cunliffe St, Johnsonville
Wellington, New Zealand
Ph/Fax (877) 456-1205