MSAccess PassThrough Query to SQL Server with Parameters

[seagreen]

Hello,
I have been searching (a lot!) to find a solution to being able to prompt for parameters for a SQL Pass Through query from Access to SQL Server. I found examples using stored procedures on the server but I do not have write access to the server in this case. I don't have create procedure permission either.

Many examples I've seen use forms but I have not found an example where I could prompt for a data range (start and end dates) and build them into the query before I pass the query to Sql Server.

I was able to set up the ODBC connection and run a query without parameters. I gather from my two day search that many have this question. I was disappointed that every answer I looked at either involved a stored procedure on the server (which I cannot do due to lack of privileges) or used forms but not in a way that seemed to apply to my situation.

Does anyone have a clear example? I don't have much experience with VBA yet but am trying to learn.

Thanks in advance for your time and help!

 

[james_lankford]

not a lot of experience with pass through queries
but this works in "normal" access queries

select 
    some_columns
from
    your_table
where 
    your_date_column between [From Date] and [To Date]

just make a query in access, put that code into it (editing it of course for the correct names), save it and run that query

leave [Fom Date] and [To Date] in brackets and it will prompt you for values

 

[seagreen]

Thanks James. yes, I wish that would have worked but I'm afraid it won't when using a pass through query to communicate with a remote server. I wish I had permission to create stored procedures on the server but they won't let anyone have that. We have read only. That's usually adequate but not in this case. I was hoping there was another way. Maybe using DAO or ADO (I'm guessing here, I've just read a little about them).

If anyone who has expertise in this has time to respond, I'd really appreciate it. I'll continue to look and if I find something I'll post again.

 

[james_lankford]

I got this to work to MySQL. I'm sure SQL Server is similar.

First, I had make a query with no parameters in Access that connected to MySQL and returned results. Then I made a form in Access, set the query as the Record Source, and set the Form format to Datasheet.
After you set the Record Source in design mode, a list of column names comes up. Drag them and place them somewhere on the form, doesn't really matter where for a datasheet.
Then go back to the Form's properties and blank out the Record Source.

then right click on the form, Build Event, Code and paste this in there.

the important thing is the connection string property
you use it to open a connection (duh)
then execute a query which returns a set of records,
then tell the form to use that recordset to populate its datasheet

then when the form is unloaded from memory, you grab the connection and close it

option explicit
'***************************************************
Private Sub Form_Open(Cancel As Integer)
    
On Error GoTo error_handler
    
    Debug.Print "Form_Open"
    
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Dim param as string
    
    Dim strConnection As String
    
    strConnection = "Driver=MySQL ODBC 5.1 Driver;SERVER=localhost;UID=jamesl;PWD=xxxxxx;DATABASE=xxxxxx;PORT=3306"
    
    'Create a new ADO Connection object
    Set cn = New ADODB.Connection
    
    With cn
        .ConnectionString = strConnection
        .Open
    End With
    
    param = "j"
    
    'Create an instance of the ADO Recordset class, and set its properties
    Set rs = New ADODB.Recordset
    With rs
        Set .ActiveConnection = cn
        .Source = "select * from my_table where first_name like '" & param & "%' "
        .LockType = adLockOptimistic
        .CursorType = adOpenKeyset
        .CursorLocation = adUseClient
        
        .Open
    End With
      
    'Set the form's Recordset property to the ADO recordset
    Set Me.Recordset = rs
    Set rs = Nothing
    Set cn = Nothing
    
exit_form_open:
        
    Exit Sub
    
error_handler:
    
    Debug.Print Err.Description
    Resume exit_form_open
    
End Sub

'***************************************************

Private Sub Form_Unload(Cancel As Integer)
    
on error resume next
    
    Debug.Print "Form_Unload"
    
    'Close the ADO connection we opened
    Dim cn As ADODB.Connection
    
    Set cn = Me.Recordset.ActiveConnection
    
    cn.Close
    
    Set cn = Nothing
    
End Sub

http://support.microsoft.com/kb/281998

 

[seagreen]

Thanks James!! This looks perfect. I can't wait to try this out. I'll post again on Monday when I've tried it (I'm afraid I cannot access the SQL server box from home).

 

[seagreen]

Thanks again James! This worked great. Sorry that I didn't get to post yesterday, the server was down so I couldn't test out your code until an hour ago. After changing the connection string and adding my own query to the database, I was in. Thanks SO much!