Comparison of approach SQL=>Access
Hi All,
SQL server 2008 with stored procedures returning data, some with parameters and some without.
MS Access 2007 front end.
What are the efficiency differences, and other considerations, between:
(1) Passthrough query with "EXEC sp"
(2) DAO connection with executed SQL "EXEC sp" & parameters, returning a recordset
(3) ADO connection, making use of the object model and adding parameters, returning a recordset
Thanks
SQL server 2008 with stored procedures returning data, some with parameters and some without.
MS Access 2007 front end.
What are the efficiency differences, and other considerations, between:
(1) Passthrough query with "EXEC sp"
(2) DAO connection with executed SQL "EXEC sp" & parameters, returning a recordset
(3) ADO connection, making use of the object model and adding parameters, returning a recordset
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Pass Through queries have a Connection property, which sometimes must be reset if you deploy to your end users. If you use a DSN then you could use the DSN in the connection property - however, you'd have to create that DSN on the user machine, or you'd have to insure it exists (and points to the right database).
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I agree that it's no more difficult, it's just one more step that needs to be handled.
ASKER
Thanks all. Great insight.
ASKER
Scott ==> Can you expand a bit on your deployment point. Why would the pass through queries need a new connection string? Could I not add an ODBC connection to my user's network profiles?