[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 678
  • Last Modified:

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

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?
  • 4
  • 3
1 Solution
WHERE ( [dbo.tbl_customer.cust_name] LIKE [some variable] = 'to the control'+'%' )
WHERE ( [dbo.tbl_customer.cust_name] LIKE 'to the control'+'%' )
jdfullerAuthor Commented:
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?
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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) = ''



      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
jdfullerAuthor Commented:
so u r binding form to newly created table created with sp?
pass thru query with stored procedure = result normal query
jdfullerAuthor Commented:
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now