Results 1 to 5 of 5
  1. #1
    cthoesen is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2018
    Posts
    3

    Using VBA code to Pass-Through a SQL Query to Network Server and Return Results in Datasheet View

    I’m using VBA code in a Module to pass SQL code as a Pass-Through query so that I can utilize Parameters to dynamically update the Month and Year within my SQL code. The updated values for Month and Year are extracted from a Text Box within a Form called Dates.

    When I run the SQL code as a stand-alone SQL query within Access, I get a list of Loan Numbers with a heading of LOANNUMBER in a Datasheet View table as expected. When I run the VBA code (below), it takes the same amount of time to run as the stand-alone SQL query and I don’t get any errors; however, once the code has completed I’m not seeing the Datasheet View table with results either.

    Is there some additional VBA code that I need to add to my code below in order to get the Datasheet View table with results to appear?

    Sub DATE_Discreet_Loan_Numbers()
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset

    Dim p1 As String, p2 As String
    Dim SQL As String

    p1 = Format([Forms]![Dates]![txtCurrentMonthDate], "MM")
    p2 = Format([Forms]![Dates]![txtCurrentMonthDate], "YYYY")

    Set qdf = CurrentDb.CreateQueryDef("")
    qdf.Connect = "ODBC;DSN=MyDSN;UID=MyUID;DATABASE=MyDatabase;Trus ted_Connection=Yes"
    qdf.SQL = "Select Distinct LOANNUMBER " _
    & "From dbo.MyTable " _
    & "Where left(EFFDATE,2) = '" & p1 & "' " _
    & "and right(effdate,4) = '" & p2 & "' " _


    & "and left(INV,1) in ('a','b','c','4','7','8')"
    qdf.ReturnsRecords = True
    Set rst = qdf.OpenRecordset

    rst.Close

    Set rst = Nothing
    Set qdf = Nothing

    End Sub

    Thank you for any and all help you may provide.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Use a string variable for the sql statement and Debug.Print the composed sql string to see what it looks like.

    I just did a test of Left(Date(),2) and it returns "3/".
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    cthoesen is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2018
    Posts
    3
    Thank you for the help

    I added a Dim sqlResults As String statement and changed the “” to sqlResults within my Set qdf = CurrentDb.CreateQueryDef("") line. Then I added Debug.Print qdf!sqlResults after my Set rst = qdf.OpenRecordset line.

    Now I get a run-time error ‘3265’: Item not found in this collection. When I hit the Debug button, it points to the Debug.Print qdf!sqlResults line.

  4. #4
    cthoesen is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2018
    Posts
    3
    Nevermind, I figured it out. I used DoCmd.OpenQuery "sqlResults" in place of the Debug.Print qdf!sqlResults line.

    I also used quotes around the sqlResults in this line: Set qdf = CurrentDb.CreateQueryDef("sqlResults")

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Glad you got it working. However, the code should have been like:

    strSQL = "SELECT ..."
    Debug.Print strSQL
    ...
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-15-2017, 10:44 AM
  2. Replies: 3
    Last Post: 08-18-2013, 09:14 PM
  3. Replies: 2
    Last Post: 03-25-2013, 09:10 AM
  4. Replies: 6
    Last Post: 11-21-2012, 05:10 PM
  5. Code to call Split Form View Datasheet
    By ahightower in forum Programming
    Replies: 1
    Last Post: 07-28-2011, 04:57 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums