Posted by bubbles on 03/05/07 04:31

Hi Guys n Gals,

I am going to migrate an Access VBA application to SQL Server 2005.

Essentially, what I need to do is to:
* open a recordset
* grab the ModelID and Age into variables
* and use these variables to compare with the next record
then
mark the current and previous records with the appropriate values.

In Access, I was able to open a recordset, step through the records
one by one and make the necessary comparisons,
calculations and updates.


Question:
=========
How can I achieve the same result with T-SQL?


Here is the code:


Private Sub UpdateRecords()
'===============
' Using DAO
'===============
Dim db as Database
Dim rs as Recordset
Dim intLastAge as Integer
Dim intLastRecID as Integer
Dim intLastModelID

Set db = Currentdb

'==============================
' Opens a recordset
' Data had been sorted by:
' ModelID, Age
'==============================
Set rs = db.OpenRecordset("someTableName")

With rs

txtLastModel = !ModelID ' Typo in prev post
intLastAge = !Age
intLastID = !RecID ' Typo in prev post
.MoveNext

Do Until .EOF


'===============================================================================
' Compare this record with the previous record
' If this record is of the same Model then
' If the Age is <= x days from the last record
' 1. Mark this record's [Comment] as "Failed"
' 2. Mark previous record's [Relate] with this
record's [RecID]
' 3. Set the current record's [Age] as intLastAge
' ELse
' 1. Set the current record's [Age] as intLastAge
' End If
' Else
' 1. Set this record's [ModelID] as intLastModelID
' 2. Set this record's [Age] as intLastAge
' End If

'===============================================================================

'=================
' If same ModelID
'=================
If !ModelID = intLastModelID Then

'=====================================================
' Check whether Age is <= 5 from the previous record
'=====================================================
If !Age <= (intLastAge + 5) Then
'==========
' Age <= 5
'==========
intLastRecID = !RecID
'=============================
' Mark [Comment] as "Failed"
'=============================
.Edit
!Comment = "Failed"
.Update


'=============================================================
' Mark previous record's [Relate] with current
record's RecID

'=============================================================
.MovePrevious
.Edit
!Relate = intLastRecID
.Update


'======================================================================
' Grab the current record's [Age] for comparison
with the next record

'======================================================================
.MoveNext
intLastAge = !Age

Else

'=================================================================
' Age not <= 5
' Grab current record's [Age] for comparison with the
next record

'=================================================================
intLastAge = !Age


End if

'=======================
' Not the same ModelID
'=======================
Else

'==================================================================
' Grab the [ModelID] and [Age] for comparison with the
next record

'==================================================================
intLastModelID = !ModelID
intLastAge = !Age

End If

.MoveNext
Loop
End With

rs.Close
Set rs = Nothing

End Sub

[Back to original message]