none
Access DLookup conversion to SQL Stored Procedure RRS feed

  • Question

  • Hi

    I am not very competent in Access so forgive me please.

    I have inherited an Access Database that uses DLookup but, I need to take this control out and into SQL Server. So all my data access will come from SQL Server, using Access as the front-end.  Will I need to create linked tables for everything?

    eg:

    if DLookup("VALUE", "lookupOPTIONS", "OPTION = 'CHECKFILEPATH'") = whatever then

    OR

    x = DLookup("VALUE", "lookupOPTIONS", "OPTION = 'CHECKFILEPATH'")

    Is it a matter of storing a comparative query in SQL server and create a stored procedure that will create a recordset? I have no idea (as you can tell). There is already an ADOLookup function written that accesses SQL  by creating a new recordsets and returns the fields collection

    '=============================================================================

    Would it be best to use the following code to retrieve my information

    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

    Many thanks

    Darryl


    ASP Developer

    Thursday, August 20, 2015 8:30 AM

Answers