Pass Variables from Access 2007 form control to SQL 2005 pass-thru query

Posted on 2009-05-01
Last Modified: 2012-05-06
ENV:  Access 2007 application talking to SQL 2005 300 miles away thru T1.  
Nice and fast BUT I need to send the value on an unbound Access form control as a filter condition inside the SQL statement; i.e., find all the estimates in 5,000 records that match customer name LIKE [Forms]![frm_search_est]![cust_text]&"*" (that would be the Access version of the LIKE command.  In SQL I can see that will translate to WHERE [dbo.tbl_customer.cust_name] LIKE (some variable = to the control)+'%' for the wildcard match.   Am I making sense here?
Question by:jdfuller
    LVL 3

    Expert Comment

    WHERE ( [dbo.tbl_customer.cust_name] LIKE [some variable] = 'to the control'+'%' )
    LVL 3

    Expert Comment

    WHERE ( [dbo.tbl_customer.cust_name] LIKE 'to the control'+'%' )

    Author Comment

    his a pass thru query.  we can't bind the control's value from the form in a pass thru query..  can we somehow declare a variable, set it to the form control value then send it in the query to the sql server for execution?
    LVL 3

    Accepted Solution

    there are two ways
    one: dynamic build pass thru query
    Sub dynamic()
        Dim qt As QueryDef
        Set qt = CurrentDb.QueryDefs("Query3") 'Query3 query to edit
            qt.SQL = "select getdate()as T;" 'T-SQL
        Set qt = Nothing
    End Sub
    seccond way
    use ado and stored procedures

    Sub dodaj()
         Dim nazwa As String
         Dim ilosc As Double
         Dim kwerenda As String
         kwerenda = "usp_dodaj"
         nazwa = "test"
         ilosc = 3.5
         Dim aPar As New ADODB.Parameter
         Dim aCom As New ADODB.Command
         Dim conn As ADODB.Connection
         Set conn = New ADODB.Connection
         conn.ConnectionString = "Provider=sqloledb;Server=PRZEMEK\SQLEXPRESS;Database=Aplikacja;Trusted_Connection=yes;" ' connection string to your server
         Set aCom.ActiveConnection = conn
             aCom.CommandText = kwerenda
             aCom.CommandType = adCmdStoredProc
             ' first parametr
             Set aPar = aCom.CreateParameter("nazwa", adVarChar,adParamInput, 100, nazwa)
             aCom.Parameters.Append aPar
             Set aPar = Nothing
             ' another parametr
             Set aPar = aCom.CreateParameter("ilosc", adDouble, adParamInput, , ilosc)
             aCom.Parameters.Append aPar
             Set aPar = Nothing
         Set aCom = Nothing
         set conn  = nothing
    End Sub

    and stored procedures

    CREATE PROCEDURE [dbo].[usp_user_check]

            @username varchar(50) = ''
          , @computername varchar(50) = ''


          SET NOCOUNT ON;

          SELECT     username, site, logintime, logout, XntVer, XntDate, termserver, computername
          FROM         tblScribeInfo
          WHERE     (username like @username + '%')


    hack :)

    you can exec stored procedure directly from pass thru query

    exec dbo].[usp_user_check] @username = 'jack sparrow'

    you got normal table with rows

    Author Comment

    so u r binding form to newly created table created with sp?
    LVL 3

    Expert Comment

    pass thru query with stored procedure = result normal query

    Author Closing Comment

    It would seem that this type of filtering or handing parameters to the SQL server should be easier because I would think that this is a super common thing to do rather than having Access bring the total table to the application before filtering.  Thank you.  Great help!

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now