VB Forum / Database Access / November 2006

Just found out I have a legit copy of VS 6 at work, so I'm trying to write a
program to do some data imports from a SQL Server 2000 datamart to a local
Access database.  Initial tests worked fine using a simpel query against the
SQL Server and writing to an Access table.

I don't have the access in the SQL Server to write my own stored procedures,
so most of my queries in SQL are fairly complex, creating various temp tables
and often writing results to a temp table that eventually gets displayed.  I
had tested displaying results from some of my SQL queries by reading in the
text from the query file and replacing the dates in the set statements.  When
I try to run my import process with one of my complex queries I either get:

1) Error 3074 - recordset object closed

or

2) If I just try to display a field from what I *think* is my query I get
the error that the field can't be found.

I'm suspecting that I can't do this process like this (read in the complex
SQL code, a few replaces to get the correct date range, and open a recordset)
- ADO is not recognizing my "final" select on the temporary results table.

Am I barking up the wrong tree?  Do I have to ask the DBA's to write me SP's
or beg that I can write my own?  Refresh my memory on how to manipulate a DTS
package (I can write those but it's been a while since I played with them in
VB)?

Any advice/illumination appreciated!