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 = !LastModel
intLastAge = !Age
intLastID = !RecordID
.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 4 2140
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
"bubbles" <bu*********@hotmail.comwrote in message
news:11*********************@30g2000cwc.googlegrou ps.com...
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?
Cursors would closely approximate what you are doing with the recordset but
I would not recommend using them.
If you are familiar with VBA you may find writing a VB.Net CLR function to
be a useful learning expirience.
The true solution is to use the power of the relational database by
developing a "set-based" solution. Joining a table to itself where RecordID
= RecordID - 1 is an easy way to compare a "current" row's data to a
previous row.
>
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 = !LastModel
intLastAge = !Age
intLastID = !RecordID
.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
>
Cursors would closely approximate what you are doing with the recordset but
I would not recommend using them.
If you are familiar with VBA you may find writing a VB.Net CLR function to
be a useful learning expirience.
The true solution is to use the power of the relational database by
developing a "set-based" solution. Joining a table to itself where RecordID
= RecordID - 1 is an easy way to compare a "current" row's data to a
previous row.
I'll try this.
Thanks!
>
=
On Mar 5, 2:12 pm, "Russ Rose" <russr...@hotmail.comwrote:
"bubbles" <bubbles....@hotmail.comwrote in message
news:11*********************@30g2000cwc.googlegrou ps.com...
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?
Cursors would closely approximate what you are doing with the recordset but
I would not recommend using them.
If you are familiar with VBA you may find writing a VB.Net CLR function to
be a useful learning expirience.
The true solution is to use the power of the relational database by
developing a "set-based" solution. Joining a table to itself where RecordID
= RecordID - 1 is an easy way to compare a "current" row's data to a
previous row.
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 = !LastModel
intLastAge = !Age
intLastID = !RecordID
.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- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
THANK YOU, RUSS!
I've used your method [x].[ID] = [x].[ID] + 1 for a few stored
procedures.
Works like a charm!
Bubbles. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Steve |
last post by:
Hi;
I have been writing a lot of short tsql scripts to fix a lot of tiny
database issues.
I was wondering if a could make an array of strings in tsql that I
could process in a loop, something...
|
by: dynoweb |
last post by:
I have several *.sql files with schema/data changes to be applied to our
current database. Is there a way to create a TSQL script that could be run
from the SQL Query Analyzer that would...
|
by: TOM GUGGER |
last post by:
OMNI GROUP
tgugger@aimexec.com
T-SQL/ CONTRACT TO PERM/ ATLANTA
|
by: Krist |
last post by:
Hi DB2 gurus,
We are considering to port our application (Windows User Interface +
MsSQL2k backend ) into DB2/400 backend , same interface.
Is there any tools/product that could make this...
|
by: Filips Benoit |
last post by:
Dear all,
Tables: COMPANY: COM_ID, COM_NAME, .....
PROPERTY: PRP_ID, PRP_NAME, PRP_DATATYPE_ID, PRP_DEFAULT_VALUE
( nvarchar)
COMPANY_PROPERTY: CPROP_COM_ID, CPROP_PRP_ID, CPROP_VALUE...
|
by: kdilip41 |
last post by:
Hi All,
I would like to whether the following details is possible ::
1. 'How Many TSQL Statements Is Executed within a particular time frame say 8 hours and at the end of 8 hours i want a...
|
by: David Lozzi |
last post by:
I'm fairly new to ASP.Net 2.0 SQLDatasource objects. It defaults using TSQL
statments for the SELECT, INSERT, UPDATE, DELETE commands, which is great
and it works. However, I've always been taught...
|
by: dharper |
last post by:
Hi!
I'm new to sql 2005 and need to run an automated report that is a fairly simple fixed width ragged right report. Problem is I need a header in the report that has static text, plus the rowcount...
|
by: Sagaert Johan |
last post by:
Hi
How can i create an sql server login (sql 2005 express) ?
I have my TSQL code ready but how can i run it from c#?
How can i execute TSQL code from Csharp, or are there better ways using some...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |