Trying to make a parameter pass through query in access/sql server

[jrdnoland]

Using Access 2016 and sql server 14.

I'm trying to make a query that I can provide parameters to and get back the result (in this case a person's name)

Here is the sql version of the query running in access:

Sub Run_Qry_PT_LABS(Counter As Integer, Request As Integer, Release As Integer)
Dim qry As DAO.QueryDef
Dim strSQL As String
Dim SQL As String

SQL = ""
SQL = "SELECT DISTINCT rdLab.tblEmployee.[Assigned_Name] FROM (rdLab.tblEmployee INNER JOIN rdLab.tblAssignedPerson_Link "
SQL = SQL & " ON rdLab.tblEmployee.[Employee_ID] = rdLab.tblAssignedPerson_Link.[APL_Link_Person]) INNER JOIN rdLab.tblTestRequests"
SQL = SQL & " ON rdLab.tblAssignedPerson_Link.[APL_Link_Counter] = rdLab.tblTestRequests.[Counter] WHERE (((rdLab.tblEmployee.Employee_IsActive)=1)"
SQL = SQL & " AND ((rdLab.tblTestRequests.[Counter])=" & Counter & ")"
SQL = SQL & " AND ((rdLab.tblTestRequests.[Project_Request])=" & Request & ")"
SQL = SQL & " AND ((rdLab.tblTestRequests.[Project_Release])=" & Release & "))"

strSQL = "Exec SQL"

End Sub

I don't even care if its a stored procedure in sql server, what I need is a way of making this query run while providing 3 parameters each time it is run. I'm not real familiar with pass through queries or sql server stored procedures. Was hoping to try here and get some information to help me move forward.

[ranman256]

usu you send the parameters to the Stored proc:

sSql = "Exec qMyStoredProc" arg1, arg2, arg3

[jrdnoland]

usu you send the parameters to the Stored proc:

sSql = "Exec qMyStoredProc" arg1, arg2, arg3

I couldn't seem to get that to work. do you include the schema in the qMyStoredProc? I would appreciate a little more detail in an answer.

[Gicu]

When working with a pass through query you need to provide the connection string (if your front-end already has linked SQL Server tables you can "borough" the connect string from one of the tables), the SQL statement and the ReturnsRecords property
.
In your example you have not done any of these, please examine these links and come back if you get stuck. You can create a new QueryDef in code (see link) or use a saved query (that already has the Connect and ReturnsRecords properties set) and just manipulate its SQL property:

https://docs.microsoft.com/en-us/off...s-property-dao
https://docs.microsoft.com/en-us/off...def-method-dao

Cheers,

[jrdnoland]

When working with a pass through query you need to provide the connection string (if your front-end already has linked SQL Server tables you can "borough" the connect string from one of the tables), the SQL statement and the ReturnsRecords property
.
In your example you have not done any of these, please examine these links and come back if you get stuck. You can create a new QueryDef in code (see link) or use a saved query (that already has the Connect and ReturnsRecords properties set) and just manipulate its SQL property:

https://docs.microsoft.com/en-us/off...s-property-dao
https://docs.microsoft.com/en-us/off...def-method-dao

Cheers,

I tried this:

Sub test()

Call modMorePassThru.stored_procedure(1502, 1, 1)

End Sub
Sub stored_procedure(Counter As Integer, Request As Integer, Release As Integer)
Dim dbs As Database
Dim rst As Recordset
Dim qdfPassThrough As QueryDef
Dim strSql As String

' Modify this line to include the path to Northwind
' on your computer.
Set dbs = Application.DBEngine(0).Databases(0)
' Create a named QueryDef based on the SQL statement.
dbs.QueryDefs.Delete "NewQry"

Set qdfPassThrough = dbs.CreateQueryDef("NewQry")
         
strSql = ""
strSql = strSql & "SELECT DISTINCT rdLab.tblEmployee.Assigned_Name, rdLab.tblTestRequests.Counter, rdLab.tblTestRequests.Project_Request, rdLab.tblTestRequests.Project_Release"
strSql = strSql & " FROM rdLab.tblAssignedPerson_Link INNER JOIN"
strSql = strSql & " rdLab.tblEmployee ON rdLab.tblAssignedPerson_Link.APL_Link_Person = rdLab.tblEmployee.Employee_ID INNER JOIN"
strSql = strSql & " rdLab.tblTestRequests ON rdLab.tblAssignedPerson_Link.APL_Link_Counter = rdLab.tblTestRequests.Counter"
strSql = strSql & " WHERE (rdLab.tblTestRequests.Counter = " & Counter & ") And (rdLab.tblTestRequests.Project_Request = " & Request & ") And (rdLab.tblTestRequests.Project_Release = " & Release & ")"
strSql = strSql & " ORDER BY rdLab.tblEmployee.Assigned_Name"

' Create a temporary snapshot-type Recordset.
Set rst = qdfPassThrough.OpenRecordset(dbOpenSnapshot)

' Populate the Recordset.
rst.MoveLast
 
' Delete the QueryDef because this is a demonstration.
dbs.QueryDefs.Delete "NewQry"
 
dbs.Close

End Sub

I'm getting a must have at least one destination field error.
I'm just not sure how to work with querydefs.

[Gicu]

Please try this (I cannot test your SQL statement without seeing your tables, make sure it works). The code assumes that you have at least one SQL server linked table in your current db that you can use to get the connection string needed for the new query; if you do not have have you have to create a valid connection string to access your SQL database.

Don't confuse pass-through queries (which reside in the Access front-end but are written in the back-end database syntax, in your case SQL Server) with stored procedures which reside in the back-end and can accept parameters as explained by Ranman.

Sub test()Dim sAssignedName as string


sAssignedName = fnPass_Through(1502, 1, 1)


End Sub


Public Function fnPass_Through(Counter As Integer, Request As Integer, Release As Integer) as String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdfPassThrough As DAO.QueryDef
Dim strSql As String


' Modify this line to include the path to Northwind
' on your computer.
'Set dbs = Application.DBEngine(0).Databases(0)
Set dbs=CurrentDB
' Create a named QueryDef based on the SQL statement.
If QueryExists("NewQry") Then dbs.QueryDefs.Delete "NewQry"


Set qdfPassThrough = dbs.CreateQueryDef("NewQry")
         
strSql = ""
strSql = strSql & "SELECT DISTINCT rdLab.tblEmployee.Assigned_Name, rdLab.tblTestRequests.Counter, rdLab.tblTestRequests.Project_Request, rdLab.tblTestRequests.Project_Release"
strSql = strSql & " FROM rdLab.tblAssignedPerson_Link INNER JOIN"
strSql = strSql & " rdLab.tblEmployee ON rdLab.tblAssignedPerson_Link.APL_Link_Person = rdLab.tblEmployee.Employee_ID INNER JOIN"
strSql = strSql & " rdLab.tblTestRequests ON rdLab.tblAssignedPerson_Link.APL_Link_Counter = rdLab.tblTestRequests.Counter"
strSql = strSql & " WHERE (rdLab.tblTestRequests.Counter = " & Counter & ") And (rdLab.tblTestRequests.Project_Request = " & Request & ") And (rdLab.tblTestRequests.Project_Release = " & Release & ")"
strSql = strSql & " ORDER BY rdLab.tblEmployee.Assigned_Name"


qdfPassThrough.Connect= dbs.TableDefs("tblOneOfSQLServerLinkedTables").Connect ' you need to set the connection string for the new query 
qdfPassThrough.SQL=strSql 'Set the SQL property of the pass-through query to the one you just built
qdfPassThrough.ReturnsRecords=True


' Create a temporary snapshot-type Recordset.
Set rst = qdfPassThrough.OpenRecordset(dbOpenSnapshot)


' Populate the Recordset.
'rst.MoveLast
if rst.RecordCount=0  then GoTo Exit_fnPass_Through 'the pass-through returned no records 


fnPass_Through=rst("Assigned_Name")


Exit_fnPass_Through:
dbs.QueryDefs.Delete "NewQry"
Set rst=nothing
Set qdf=Nothing
dbs.Close
Set dbs=Nothing


End Function


Function QueryExists(strQueryName As String) As Boolean
Dim qdfLoop As DAO.QueryDef


For Each qdfLoop In CurrentDb.QueryDefs
   If strQueryName = qdfLoop.Name Then
    'Debug.Print Now, "[QueryExists] Query '" & strQueryName & "' exists"
   QueryExists = True
   Exit For
   End If
Next qdfLoop
End Function

Cheers,

[jrdnoland]

I do have linked access tables. I don't want to reopen the database this query to a stored procedure occurs when the database is open. The query I have I was able to make it run in sql server as it is written. That's why the schema was made to be part of the query/stored procedure.

[Gicu]

Have you tried what I've sent you in the last post?

Cheers,

[jrdnoland]

Have you tried what I've sent you in the last post?

Cheers,

Yes, but I couldn't get past this part
Set dbsCurrent = OpenDatabase("DB1.mdb")

I don't want to reopen the database, but work with the currently open database, which is an .accdb file.

[Minty]

Well simply change it to

Set dbsCurrent = CurrentDb

?

[jrdnoland]

Have you tried what I've sent you in the last post?

Cheers,

I tried it again using the currentDB and got it to work.

Thanks, to all for the help, much appreciated!

[Gicu]

Glad to hear you got it working (I am pretty sure I did not have OpenDatabase("DB1.mdb") in the code I posted).

Good luck with your project!