473,320 Members | 1,945 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Need Help migrating VBA to TSQL

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

Mar 5 '07 #1
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

Mar 5 '07 #2

"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

Mar 5 '07 #3
>
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!

>
=

Mar 5 '07 #4
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.

Apr 20 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
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...
2
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...
1
by: TOM GUGGER | last post by:
OMNI GROUP tgugger@aimexec.com T-SQL/ CONTRACT TO PERM/ ATLANTA
6
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...
7
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...
0
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...
8
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...
0
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...
1
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...
0
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...
1
isladogs
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...
0
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...
0
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...
0
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...
1
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)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
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....
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.