Running a SQL Query with Parameters From Access 2007

[Bulzie]

I have a SQL Query in Access 2007 that works fine when I run it with the criteria hardcoded. But not sure how to Pass it a parameter and get it to work. I have googled numerous sites that talked about using QryDefs and ADO/DAO but cannot seem to piece all the steps together to make it work. My SQL query in Access called qryTest1 contains EXEC [sp_TEST1] '11' which is a Stored procedure on my SQL Server and runs fine. But for the '11' criteria is the parameter I will need to pass from Access to this SQL query. It will be a value based on a field in my Access database. Any ideas? Oh and I just found this site so not new to MS Access but new to this site

[pbaldy]

If I'm using a pass through query, I update the SQL with a QueryDef object. Sometimes I'll use an ADO command object. Since it sounds like you're already using a query, try the QueryDef method that you found.

[ItsMe]

I extracted some code from one of my DB's. Something like the following would be my best guess ...

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
     
        Set db = CurrentDb
        Set qdf = db.CreateQueryDef("")
        strSQL = "EXEC [sp_TEST1] '" & Me.ControlName.Value & "'"
        
        qdf.Connect = MyConnectionString
        qdf.SQL = strSQL
        qdf.ReturnsRecords = False
        qdf.Execute dbFailOnError + dbSeeChanges

ExitHere:
    Set qdf = Nothing
    Set db = Nothing

[pbaldy]

I use a function that just updates the SQL (and optionally the other properties). I don't execute it there, as it could be the query will be used for a report or something.

[ItsMe]

I use a function that just updates the SQL (and optionally the other properties)...

Yup, that is definitely the way. I thought I would provide some example code. I had to extract statements from one of my functions. Hopefully I did not hack things up too bad.

Interesting comment about not executing it there. I chose this example as a temp query object using "" as the name. So an example like this would be used to only execute a stored procedure that does not return records. In fact, I use the temp object technique because of a link/resource you provided for me, Paul. Thanks for that

[Bulzie]

I've tried different examples of the qdef code you gave but get the error that I cannot use for a Select statement which it is. It will return records based on the parameter criteria. Maybe I need to load that into a Recordset variable?

[ItsMe]

I created an SP

CREATE PROCEDURE [dbo].[proc_CreateEmailTemp]
 @EmailTypeKey tinyint = 0,
 @EmailDesc nvarchar(254)
AS
BEGIN
 INSERT INTO tblEmails (EmailTypeKey,EmailDesc)
 VALUES (@EmailTypeKey, @EmailDesc);
END;
GO

Then I created a sub in Access

Public Sub ThisSQL(strSQL As String)
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
'    Dim strSQL As String
     
        Set db = CurrentDb
        Set qdf = db.CreateQueryDef("")
'        strSQL = "EXEC [sp_TEST1] '" & Me.ControlName.Value & "'"
        
        qdf.Connect = MyConnectionString
        qdf.SQL = strSQL
        qdf.ReturnsRecords = False
        qdf.Execute dbFailOnError + dbSeeChanges
ExitHere:
    Set qdf = Nothing
    Set db = Nothing
End Sub

Then I called the sub.

Dim strExecProc As String
strExecProc = "EXEC [proc_CreateEmailTemp] 209, 'wsdfg'"
ThisSQL strExecProc

Did not have an issue. Where did your code throw the exception. Maybe post the Stored Procedure here.

[Bulzie]

This is my sproc:

ALTER PROCEDURE [dbo].[sp_TEST1]
--ALTER PROCEDURE [dbo].[]
(
@ID NUMERIC(2)
)
AS
BEGIN
--/* DEBUG CODE
Select * From tbLDCensus WHERE ID = @ID
--*/

END

So when I execute this in SQL using EXEC [sp_TEST1] '11', I get a record back.

I created a SQL pass-through query in Access with the same code EXEC [sp_TEST1] '11' and it returns the record. But I cannot figure how to write code to pass it the '11' variable in stead of the hardcoded value. It either does not work or tells me it cannot be a Select query.

I will try to use your example above and see what it does.

[Bulzie]

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb
Set qdf = db.CreateQueryDef("")
strSQL = "EXEC [sp_TEST1] '" & 11 & "'" <========== just putting in 11 to see if it will work, if it does then can put in a variable.

qdf.Connect = MyConnectionString
qdf.SQL = strSQL <============ gives SQL error here saying needs INSERT, DELETE, UPDATE, SELECT..."
qdf.ReturnsRecords = True
qdf.Execute dbFailOnError + dbSeeChanges
ExitHere:
Set qdf = Nothing
Set db = Nothing

[ItsMe]

EDIT: I just noticed something that is not likely going to work with your stored procedure. You are going to need to return some values. I should have an example somewhere. Or maybe Paul has one. /EDIT


I see. Like Paul mentioned, you can assign SQL to an existing query. Also, it seems you may not be providing the correct connection string and also, you are not providing the correct SQL syntax.

So, start with building a query object and give it a special name. Then paste some nonsense SQL into SQL view like
SELECT * from tblNotExist

After you save your object. Use the DAO example, but use the name of your Query Object and do not execute the query object. You need to concatenate your variable and that is what the double quotes were for in the previous example. If you want to hardcode a value and not use a variable like a textbox control, just place the value directly in the string.

So something like the following should work.

Public Sub ThisSQL(strSQL As String)
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
     
        Set db = CurrentDb
        Set qdf = db.QueryDefs("qryMeaningfulName")
        'Set qdf = db.CreateQueryDef("qryMeaningfulName")
        'strSQL = "EXEC [sp_TEST1] '11'"   'if the column is of type text.
'or
strSQL = "EXEC [sp_TEST1] 11"
        
        qdf.Connect = MyConnectionString
        qdf.SQL = strSQL
        
ExitHere:
    Set qdf = Nothing
    Set db = Nothing
End Sub

[pbaldy]

qdf.SQL = strSQL <============ gives SQL error here saying needs INSERT, DELETE, UPDATE, SELECT..."

I change the SQL of an existing pass through query. The error would imply it's treating the query you're creating as a regular Access query.

[ItsMe]

Well, I was able to create a stored procedure that returned a single record. However, it seems like a bad idea to use a stored procedure. It seems to me what you really want is a View. Anyway ...

The stored procedure looks like this.

CREATE PROCEDURE [dbo].[proc_CreateEmailTemp]
    @EmailType tinyint,
 @EmailTypeKey tinyint OUTPUT,
 @EmailDesc nvarchar OUTPUT
 
AS
BEGIN
 SET NOCOUNT ON;
 SELECT @EmailDesc = tblEmails.EmailDesc, @EmailTypeKey = tblEmails.EmailTypeKey
 FROM tblEmails
 WHERE tblEmails.EmailTypeKey = @EmailType;
RETURN
END;

GO

The SQL looks like this

declare @EmailType tinyint, @TypeKey tinyint, @Description nvarchar; 
EXEC [proc_CreateEmailTemp] 1, @EmailTypeKey = @TypeKey output, @EmailDesc = @Description output; 
SELECT @TypeKey as EmailTypeKey, @Description as EmailDesc;

So the VBA string is this

Dim strExecProc As String
strExecProc = "declare @EmailType tinyint, @TypeKey tinyint, @Description nvarchar; " & _
              "EXEC [proc_CreateEmailTemp] 1, " & _
              "@EmailTypeKey = @TypeKey output, @EmailDesc = @Description output; " & _
              "SELECT @TypeKey as EmailTypeKey, @Description as EmailDesc;"