Newbie Questions About DAO Queries to SQL Server 
Author Message
 Newbie Questions About DAO Queries to SQL Server

I hope someone can provide guidance on the following matter.  The
object of the exercise is to drive Microsoft Access reports with
pass-through queries to SQL Server 2000.  However, before the reports
can be started, the actions of a third party security package must be
executed on the connection to SQL Server.  The third-party security
package is based on ADO technology, but because ADO record sets can't
drive Access reports, I need to reverse engineer some functionality to
use DAO.  The following flow diagram illustrates the DAO interaction
between MS Access and SQL Server that needs to be implemented:

(1) MS Access -- execute stored procedure --> SQL Server
(2) MS Access <------ returns record set ------- SQL Server
(3) MS Access ---- execute sp_setapprole ----> SQL Server
(4) MS Access ---- select CURRENT_USER ----> SQL Server
(5) MS Access <------ returns record set ------- SQL Server
(6) MS Access ------- query for report --------> SQL Server
(7) MS Access <------ returns record set ------- SQL Server

My thoughts are to retrieve the record set for steps (1)/(2) with
ODBCDirect Connection, QueryDef, and RecordSet.  Do I need to close
the RecordSet before proceeding to step (3)?  For step (3), can I
change the SQL for the QueryDef and call its Execute method, or do I
need to create a new QueryDef?  Are there specific, or preferred,
options I need to set?  Steps (4)/(5) would be handled like (1)/(2).

Thank you in advance for your assistance.
Regards, Peter
N.B. E-mail address contains spam trap; remove /*NOSPAM*/.

Wed, 13 Oct 2004 07:20:51 GMT  
 [ 1 post ] 

 Relevant Pages 

1. Newbie needs help with DAO SQL Query Problem

2. Newbie : SQL query question

3. Newbie question: Using SQL query for report

4. Newbie Question: Access vs SQL Server

5. Newbie Question: How to open a SQL server

6. Newbie question--confused about SQL server/ADO vsDAO

7. Newbie SQL Server Question

8. Newbie Question - SQL Server

9. SQL server query question

10. SQL Server Parameter Query Question

11. Sql - Dates query to SQL Server and access

12. SQL queries or DAO recordset?


Powered by phpBB® Forum Software