JM wrote:
Hello,
Using Access 2000 queries, you can reference(pass) form values directly
using syntax like Forms!frmPaint!txtColor. I want to do a pass
through query to SQL Server 2000, but I don't know how to pass the form
values to SQL server without resorting to VB code. These canned
queries populate other elements of the form, and that's another reason
I don't want to write additional code. These queries were originally
set up merely using SQL server as the ODBC target database, but the
Access SQL language translation (I think) was making them take 5 times
the amount of time that they did with the old Access DB. The originals
used "IIf" a lot, for example, and that probably has to be translated
to a dialect SQL Server understands.
Tough titty :-)
Upsizing to SQL Server was never and will never be a painless task to
get the best out of it. There really are no short cuts, at most you can
usually have a two table Access query with a join that Access will
translate for you into T-SQL, any more complex than that and it will not
know what to do. I have seen several times a simple query with 3 tables
and 2 joins execute painfully slow as Access prepares and executes a
stored procedure for each row, potentially many times per row if there's
criteria involved.
About the only short cut you can take with your problem (no guaruntees
mind you), is to write a view on the server to take care of all the
joins, etc then do a query in Access to add in the forms! references.
Otherwise you may find reverting to VBA to create pass-through queries
will be the fastest way.
--
This sig left intentionally blank
