|
|
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
Navigation:
[Reply to this message]
|