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

x
  • 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?
0
jdfuller
Asked:
jdfuller
  • 4
  • 3
1 Solution
 
prachwalCommented:
WHERE ( [dbo.tbl_customer.cust_name] LIKE [some variable] = 'to the control'+'%' )
0
 
prachwalCommented:
WHERE ( [dbo.tbl_customer.cust_name] LIKE 'to the control'+'%' )
0
 
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?
0
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!

 
prachwalCommented:
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
     conn.Open
     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
         aCom.Execute
         conn.close
     Set aCom = Nothing
     set conn  = nothing
End Sub

and stored procedures

CREATE PROCEDURE [dbo].[usp_user_check]

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

      

AS
BEGIN
      SET NOCOUNT ON;

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

END

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

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