Passthrough query in VB 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]

 

[Guest_imported]

Here is what I have done to debug this type of problem.
Add this to your first section

Dim sql as string
sql = "your sql statement goes here"
debug.print sql

set a break point after the debug.print sql
paste the results from the debug window into a query sql view window, paste your connection info into the property sheet of that query and click the data sheet view of the query. If there is anything wrong with your syntax you should get a good error message that will help.

This works great for me!

Good luck.
John [sig]<p>John A. Gilman<br><a href=mailto:gms@uslink.net>gms@uslink.net</a><br>[/sig]