Passing parameters to a pass-through query running on a remote machine

[klaromulus]

Does anyone know if it is possible in MS Access 2000 to pass parameters
to a pass through query running on a remote server.



Here's my dilemma:

In my VB code for MyForm I do a "DoCmd.Openquery XYZ" which has the ODBC
connection string built into it to connect to my production data server.
The sql in the query just says SELECT * FROM Mytable;

This table is just starting out on Nov. 1 but is going to grow very
soon. With it set up this way I (or whoevers uses this application) will
be pulling back the whole table each time and thats not good. MyForm has
a start_date and end_date that I really only want to pull back data for
from the remote server. Its not my server and I dont want to put a
stored procedure on it. I want to somehow pass the start_date and
end_date that the user has entered to use in retrieving the data from
the remote server using this pass-through query.



Does anyone have any ideas how I might code this? I dont have any flashy
stuff in my code and just use the basic JET database tools (i.e. no ADO,
or other drivers). Thanks, Kyle

 

[Kristi]

Kyle,

It's not exactly what you were asking for, but you might consider altering the SQL query you pass based on the date data collected in your form. This assumes that the SQL table (Mytable) has a date field to compare against for the date range you are collecting in your form... Maybe Mytable has a SQL Date field called "MytableFieldForDate". If this is the case - something like the following might work for you:

Dim sMySQLStatement as String
sMySQLStatement = "Select * from Mytable where MytableFieldForDate >= start_date and MytableFieldforDate <= end_date"

If the SQL table field MytableFieldForDate is defined as a SQL Date, you would need to adjust the query string so that the query passed is compared appropriately against the SQL Date values stored in "MytableFieldForDate"... Something like:

sMySQLStatement = "Select * from Mytable where MytableFieldForDate >= " & "Convert(datetime , " & "'" & start_date & "'" & ")" & " and MytableFieldforDate <= " & "Convert(datetime , " & "'" & end_date & "'" & ")"

If start_date and end_date were string values collected from your form (e.g.., "11/07/03" and "11/08/03") the result of sMySQLStatement string would look like:

Select * from Mytable where MytableFieldForDate >= Convert(datetime , '11/07/03') and MytableFieldforDate <= Convert(datetime , '11/08/03')

In your ODBC connection string the sql query would simply be the string sMySQLStatement. Hope this helps. Kristi