Convert logic in a dynamic query from Access VBA to SQL Server view

Question

I am converting an Access app to a web app, and I am having trouble converting this vb/SQL into views. It doesn't seem like that hard problem, but it is really throwing me for a loop.

Public Function GetProjectNumber(ByVal HeaderID As Long) As String
    Dim retval As String
    Dim rst As New ADODB.Recordset
    Dim tempID As Long

On Error GoTo Err_Handler

    rst.Open "SELECT TransferID, ProjectID FROM dbo.tblProject WHERE HeaderID = " & HeaderID
    If Not (rst.EOF And rst.BOF) Then
        If IsNull(rst!TransferID) Then
            retval = rst!ProjectID
        Else
            tempID = rst!TransferID
            If rst.State = adStateOpen Then rst.Close
            rst.Open "SELECT ProjectID FROM dbo.tblProject WHERE HeaderID = " & tempID
            If rst.EOF And rst.BOF Then
                retval = "Transfer from ????"
            Else
                retval = "Transfer from " & rst!ProjectID
            End If
        End If
    End If

If rst.State = adStateOpen Then rst.Close

Exit_Handler:
    Set rst = Nothing
    GetProjectNumber = retval
    Exit Function

End Function

I tried using a nested case statement, but cases can't be applied to each row returned. Is there a way to do this with IIF? Or to make this a function?

Wouldn't it be easier to import the MSAccess DB into SQLserver using SQLserver's import tools? — ESP32
– ESP32, Commented Apr 26, 2016 at 14:16
It isn't a Access database, it is a Access front end hitting a SQL 2012 database. I am trying to rewrite the front end as a web application, but need to take any SQL calls in Access and move them to stored procedures/views. — Alex
– Alex, Commented Apr 26, 2016 at 14:22
No matter the solution you need to read about, understand and start using parameterized queries. Building up strings and executing them directly against you server is an open invitation for bobby tables to come visit. bobby-tables.com — Sean Lange
– Sean Lange, Commented Apr 26, 2016 at 15:37

Thomas G · Accepted Answer · 2016-04-27 08:12:38Z

3

The following query mimicks your code for all records in the table (ie for all HeaderIDs).

SELECT P1.HeaderID,
(CASE 
    WHEN P1.TransferID IS NULL THEN P1.ProjectID
    WHEN P2.ProjectID IS NULL THEN 'Transfer from ???' 
    ELSE "Transfer from " + P2.ProjectID
 END) AS Returns
FROM dbo.tblProject P1
LEFT JOIN dbo.tblProject P2 ON P1.TransferID = P2.HeaderID

If you make a SQL-server view out of it, you can query it easily with a specific HeaderID, like you do in your VBA sub

SELECT Returns FROM the_view WHERE HeaderID='xxxxx'

And it should returns exactly the same result as your procedure

edited Apr 27, 2016 at 8:12

answered Apr 26, 2016 at 14:28

Thomas G

10.3k7 gold badges33 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Smandoli Over a year ago

This answer addresses the critical issue: writing set-based code. Set-based code means thinking in COLUMNS. Thinking in COLUMNS is agreeable with SQL. Thinking in ROWS puts you at odds with SQL, frustrates your work. If you can use plain SQL to replace the row-by-row processing of a VBA recordset (even in Access), you are going in the right direction.

Alex Over a year ago

Seeing it now makes more sense than performing row by row manipulation like vba. unfortunately, I'm working with 20+ years of code written by numerous people and this is just 1 of 14 functions that are part of a 'master query' that need to be translated over

Collectives™ on Stack Overflow

Convert logic in a dynamic query from Access VBA to SQL Server view

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related