Pass through query in code

[mswarner]

I'm trying to use code to execute a pass through query with parameters. I got the procedures from Microsoft's site. Here's my code:

Option Compare Database
Option Explicit

      Function ParamSPT(StartRange As Long, EndRange As Long)

         Dim MyDb As Database, MyQ As QueryDef, MyRS As Recordset
         Set MyDb = CurrentDb()

         ' Create a temporary QueryDef object that is not saved.
         Set MyQ = MyDb.CreateQueryDef("")

         ' Type a connect string using the appropriate values for your
         ' server.
         MyQ.Connect = "ODBC;DSN=MELANIE'S;UID=MELSWA;PWD=BETSY45;"


         ' Set the SQL property and concatenate the variables.
         MyQ.SQL = "SELECT ORD#MD, SUBSTR(R256MD,7,45) as INK  FROM" _
         & "MAXDATA066.SMFGD  WHERE OSQ#MD = 2 AND ORD#MD BETWEEN " _
         & StartRange & " AND " & EndRange

         ' Set ReturnsRecords to false in order to use the Execute method.
         MyQ.ReturnsRecords = True
         Set MyRS = MyQ.OpenRecordset()
         MyRS.MoveFirst

         Debug.Print MyQ.SQL
         MyQ.Close
         MyRS.Close
         MyDb.Close

      End Function

When I send it through debug it stops at "Set MyRS = MyQ.OpenRecordset()" with an error 3146. Help says that's a connection error.

This query works as a regular Pass Through query with the StartRange and EndRange hardcoded into the SQL. I copied my connection string from the properties of the query so it should connect fine.

Any ideas?
[sig][/sig]

 

[jimmythegeek]

What exactly are you wanting to do here? Create a query? Open a recordset to do something with it? Are you using SQL Server, Oracle, etc.?

You are kind of crossing two methods here. It looks like you are trying to create a temporary query (Set MyQ = MyDb.CreateQueryDef("&quot), but yet you are also trying to open a recordset (Set MyRS = MyQ.OpenRecordset()). Why do you want to to both?

I can give you the right syntax, I just need to know what your purpose is. [sig]<p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development[/sig]

 

[mswarner]

I'm using a pass through query to select records from an AS400 system that I'm connected to through ODBC. You can't have a pass-through query prompt you with parameters so you have to create the SQL statement through the concatenation of your statement and the parameters you want to use. See

http://support.microsoft.com/support/kb/articles/Q131/5/34.asp?LN=EN-US&SD=gn&FR=0

for more on this.

Anyway, I'm combining the resulting recordset another pass-throuhg recordset and some local database data to create a report. I tried just selecting all the records, but it's too many and it just grinds away forever. My users aren't well versed enough to just go in and alter the SQL statement so I need to be able to prompt them for the parameters. I tried doing it with a regular query (not a passthrough) where I can prompt, but it's slow. The pass-through query I have set up works great and fast, but I can't feed it parameters.
[sig][/sig]

 

[jimmythegeek]

Why are you creating a temporary query? If it is temporary, you cannot use it as a recordsource for either a report or a form. If you test to see if the PTQ exists first (if it does NOT exist, create it, if it DOES exist, delete it, and recreate it), then you can simply set the recordsource of your report as that PT Query, or combine that query with whatever you want. This way the query always exists, it is just re-created with different criteria each time.

First copy the following function into any module, and save it:

==========
Function ObjectExists(ObjType As Integer, objName As String) As Boolean
'Purpose: Determines whether or not a given object exists in database
'Example: If ObjectExists(acTable, &quot;tblOrders&quot then ...

On Error Resume Next
Dim db As Database
Dim strTemp As String, strContainer As String
Set db = CurrentDb()

Select Case ObjType
Case acTable
strTemp = db.TableDefs(objName).NAME
Case acQuery
strTemp = db.QueryDefs(objName).NAME
Case acMacro, acModule, acForm, acReport
Select Case ObjType
Case acMacro
strContainer = &quot;Scripts&quot;
Case acModule
strContainer = &quot;Modules&quot;
Case acForm
strContainer = &quot;Forms&quot;
Case acReport
strContainer = &quot;Reports&quot;
End Select
strTemp = db.Containers(strContainer).Documents(objName).NAME
End Select

ObjectExists = (Err.Number = 0)
End Function
==========

Here is your modified code, change the red accordingly:

==========
Function ParamSPT(StartRange As Long, EndRange As Long)
Const STRQUERY As String = &quot;ptqYourQueryName&quot;
Dim db As Database
Dim strSQL As String, strConnect As String
Dim qdf As QueryDef

' Set string variables
strConnect = &quot;ODBC;DSN=MELANIE'S;UID=MELSWA;PWD=BETSY45;&quot;

strSQL = &quot;SELECT ORD#MD, SUBSTR(R256MD,7,45) as INK &quot;
strSQL = strSQL & &quot;FROM MAXDATA066.SMFGD &quot;
strSQL = strSQL & &quot;WHERE OSQ#MD = 2 AND ORD#MD BETWEEN &quot; & StartRange & &quot; AND &quot; & EndRange

' Test to see if query exists, if so, delete it
If ObjectExists(acQuery, STRQUERY) Then DoCmd.DeleteObject acQuery, STRQUERY
Set db = CurrentDb()

' Re-Create your query
Set qdf = db.CreateQueryDef(STRQUERY)

qdf.Connect = strConnect
qdf.sql = strSQL
qdf.ReturnsRecords = True

db.CLOSE
End Function
==========

Now your query with the appropriate criteria should exist, so you can use it to do whatever you want now. Hope this makes sense, I have used this method successfully many times. Let me know if you have any trouble. [sig]<p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development[/sig]

 

[markphsd]

Jimmy,

Does that solution create a passthrough query or an access query?

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.