Passing SQL to MSDE/SQL server

[Brian]

In the process of migrating Access mdb fe/be to Access MDB fe / SQL (MSDE)
be. What is the best method for passing queries to the server?

Concatenate the SQL in VBA - if so, how do I pass to server (SQLExec?)?
Create/call stored procedure?

Several of my forms open very slowly in Access fe/be structure because of
the complexity of the queries behind the combo box I use as a record selector
on those forms. I allow the user to select any combination of about 12 other
combo boxes that filter the selector's RowSource on various fields. How might
one transfer the workload of returning the results of such a complex
RowSource to the SQL server?

 

[MGFoster]

In the process of migrating Access mdb fe/be to Access MDB fe / SQL (MSDE)
be. What is the best method for passing queries to the server?

Concatenate the SQL in VBA - if so, how do I pass to server (SQLExec?)?
Create/call stored procedure?

Several of my forms open very slowly in Access fe/be structure because of
the complexity of the queries behind the combo box I use as a record selector
on those forms. I allow the user to select any combination of about 12 other
combo boxes that filter the selector's RowSource on various fields. How might
one transfer the workload of returning the results of such a complex
RowSource to the SQL server?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use SQL Pass Through (SPT) queries.

Using DAO (and .mdb file not an .adp file):

Set up a query and then on the menu bar select Query > SQL Specific >
Pass Through. Then fill in the query's "ODBC Connect Str" property w/
the correct ODBC string for your SQL server.

Fill the SQL view w/ the appropriate T-SQL command. Save the query.
Use that query as the RowSource of the List/Combo Boxes.

You can change the SQL command on the fly by using something like this:

CurrentDB.QueryDefs("query name").SQL = "SELECT * FROM table_name"

Then use the "query name" any way you like.

For Access queries that have parameters, it is usually better to convert
those queries into SQL Stored Procedures (SP). Then change the call to
the sps this way:

CurrentDB.QueryDefs("query name").SQL = _
"MyStoredProc param1, param2"

Substitute the name of your sp for "MyStoredProc" and the values of the
parameters for "param1" and "param2." Be sure to use the appropriate T
SQL delimiters for each data type.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQwzXO4echKqOuFEgEQIllwCbBdJZaeC17EJQb/XFjFu03iFQyjoAnR1C
HofBqmc7zXq7a567+luHQUlL
=rjax
-----END PGP SIGNATURE-----

 

[Brian]

Thanks much. This is the jump start I needed (spared me some time searching
through the Access & SQL server docs).

 

[Bill Sturdevant]

I am exploring this issue also. Do you us a SQL Passthru in addition to
linking to the SQL Server tables?

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I use both linked tables & pass thrus. I'm leaning more toward using
just pass-thrus w/o linking the tables. This would prevent the users
from opening the SQL table by double-clicking on the linked table name.
Of course, the correct way is to use user security to deny users access
to the tables (they'd only use the querys to get data).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQzl5aYechKqOuFEgEQJXwwCeL0vZZjYnWez+qyu1rWySl/djH4UAnRJ6
WvIKljWO/Egst5W6N/HkQOvL
=95Kx
-----END PGP SIGNATURE-----