How to replace multiple DLookups with stored procs in SQL

Question

I am moving access qrys to stored procs in sql server. Any recomendations on how i should handle the DLOOKUP function ?
Any help on how should I replace all the below DLookups with single stored proc with output parameters.

Private Sub Borrower_Exit(Cancel As Integer)
    txtBorrowerCo = DLookup("CompanyNo", "LenderBorrower", "Name= Forms!frmLoanCreation!Borrower")
    txtBorrowerID = DLookup("ID", "LenderBorrower", "Name= Forms!frmLoanCreation!Borrower")
    txtBorrowerLocation = DLookup("Location", "LenderBorrower", "Name= Forms!frmLoanCreation!Borrower")
    txtBorrowerSignee = DLookup("Signee", "LenderBorrower", "Name= Forms!frmLoanCreation!Borrower") '**** 991020
    txtBorrowerTitle = DLookup("SigneeTitle", "LenderBorrower", "Name= Forms!frmLoanCreation!Borrower") '**** 991020
    Child14.Requery
End Sub

Answer 1

I think a recordset would be simpler.

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Set db = CurrentDb
    strSQL = "SELECT [CompanyNo], [ID], [Location], [Signee], [SigneeTitle] " _
            & "FROM LenderBorrower " _
            & "WHERE [Name]=" & Chr(34) & Me.Borrower & Chr(34) & ";"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
    With rs
        If Not .EOF Then
            txtBorrowerCo = .Fields(0)
            txtBorrowerID = .Fields(1)
            txtBorrowerLocation = .Fields(2)
            txtBorrowerSignee = .Fields(3)
            txtBorrowerTitle = .Fields(4)
        End If
    End With
    Set rs = Nothing
    Set db = Nothing

---

Bill Mosca
http://www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 2

With Output Parameters you need to declare each output parameter with its datatype and include it in the execution call of the stored proc.

You can do it in a simpler way;

SQL

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'Borrower_info' AND type = 'P')
   DROP PROCEDURE Borrower_info
GO
CREATE PROCEDURE Borrower_info 
@Borrower varchar(40) --Change to your needed datatype
AS
SELECT CompanyNo, ID, Location, Signee, SigneeTitle
   FROM LenderBorrower
   WHERE Name = @Borrower
GO

VBA

Dim con As ADODB.Connection     
Dim rs As ADODB.Recordset
Dim strSQL as String  
    
Set con = New ADODB.Connection     
con.ConnectionString = YourConString 
con.Open   
strSQL = "EXEC Borrower_info '" & Forms!frmLoanCreation!Borrower & "'"
 
Set rs = New ADODB.Recordset     
Set rs = con.Execute(strSQL)     
Debug.Print rs!CompanyNo, rs!ID, rs!Location, rs!Signee, rs!SigneeTitle
   
rs.Close     
Set rs = Nothing 

Answer 3

Bill Mosca [MVP] wrote:

   strSQL = "SELECT [CompanyNo], [ID], [Location], [Signee], [SigneeTitle] " _
           & "FROM LenderBorrower " _
           & "WHERE [Name]=" & Chr(34) & Me.Borrower & Chr(34) & ";"
   Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
   With rs
       If Not .EOF Then
           txtBorrowerCo = .Fields(0)
           txtBorrowerID = .Fields(1)
           txtBorrowerLocation = .Fields(2)
           txtBorrowerSignee = .Fields(3)
           txtBorrowerTitle = .Fields(4)

I'd much prefer to see txtBorrowerCo = rs!CompanyNo instead of .Fields(0).
If the query ever gets changed very strange things can happen.

Also if the txt fields aren't variants you could have a problem if the
fields are empty in the table.   I'd have to test that to double check
though.

Tony

---

Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

Answer 4

Joscion wrote:

IF EXISTS (SELECT name FROM sysobjects         WHERE name = 'Borrower_info' AND type = 'P')
  DROP PROCEDURE Borrower_info
GO
CREATE PROCEDURE Borrower_info

Why not just remove the first four lines.  Then change the CREATE PROCEDURE Borrower_info to ALTER PROCEDURE Borrower_info once you've created the stored procedure the first time?

Just curious and not trying to start an argument here.

Tony

---

Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/