By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 2,107 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

Form values via SQL Server Pass-through?

P: n/a
JM
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.

TIA,

JM

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.