N
NKTower
I have a table value function that returns the rows that I want given 3
parameters. It works just fine in SQL Management Studio Express:
select * from
dbo.tvf_Airports_in_Range_of_Lat_Long(41.29757,-73.498924,100) order by miles
The user enters a ZIP code for the origin location and a max distance, and a
second ZIP code for the destination and a max distance. The latitude and
longitude come frrm a ZIP code table lookup.
From Access 2003 the query works as a pass-through but there are two problems:
a) it insists on prompting for the System DSN every time
b) I can't see a way to pass parameters in short of re-writing the SQL
source for the query.
I've seen examples where a pass through is constructed in VBA to execute on
server but not return records. I've also seen examples where a DAO recordset
is returned. But I haven't seen any where the returned records are used as a
row source for a table (or perhaps in the future, as a row source for a
combobox.). In a pinch I suppose I could populate a lclient-side table with
the results of the returned DAO record set (typically a couple dozen) - but
that's not very elegant.
This particular form ultimately will have two sub-forms - one for airports
near departure, the other for airports near destination. I'd like to use the
same mechanism for both subforms and just pass in the 3 parameters for each.
Should I be using something other than a table value function and a pass
through query for my row source? The data returned will not need to be
updatable.
Lastly, I understand that ADP is being phased out, so I don't want to
implement in that mode.
parameters. It works just fine in SQL Management Studio Express:
select * from
dbo.tvf_Airports_in_Range_of_Lat_Long(41.29757,-73.498924,100) order by miles
The user enters a ZIP code for the origin location and a max distance, and a
second ZIP code for the destination and a max distance. The latitude and
longitude come frrm a ZIP code table lookup.
From Access 2003 the query works as a pass-through but there are two problems:
a) it insists on prompting for the System DSN every time
b) I can't see a way to pass parameters in short of re-writing the SQL
source for the query.
I've seen examples where a pass through is constructed in VBA to execute on
server but not return records. I've also seen examples where a DAO recordset
is returned. But I haven't seen any where the returned records are used as a
row source for a table (or perhaps in the future, as a row source for a
combobox.). In a pinch I suppose I could populate a lclient-side table with
the results of the returned DAO record set (typically a couple dozen) - but
that's not very elegant.
This particular form ultimately will have two sub-forms - one for airports
near departure, the other for airports near destination. I'd like to use the
same mechanism for both subforms and just pass in the 3 parameters for each.
Should I be using something other than a table value function and a pass
through query for my row source? The data returned will not need to be
updatable.
Lastly, I understand that ADP is being phased out, so I don't want to
implement in that mode.