Accessing a SQL Server database from DAO

[Funger]

Hi all. I've got a slight problem that I hope someone out there can help me with.

The overall task:
I have a SQL Server database established, and I need to be able to create a Microsoft Access 2000 mdb filled with a subset of the SQL Server data.

This process needs to be executed from an asp page.


My idea of the process:
1.) Create a View in SQL Server for each of the tables that needs to come down
2.) Write a function in VB that does the following:
2a.) Creates a blank mdb on the server
2b.) Establishes a connection to the SQL Server database
2c.) Cycles through the Views and executes an "INSERT" SQL Statement
3.) Allow the user to download the created mdb off of the server


What I have so far:
1.) I've created all of the views for the tables.
2.) Using Microsoft ActiveX Data Object Library, I can connect to the SQL Server database.

What I don't know how to do:
1.) Cycle through the Views on my SQL Server connection.
2.) Write a SQL statment that transfers the data from the connection/view into the mdb.



Any help would be appreciated!

 

[Calvin]

you need to dig into your sql server documentation and write a DTS package or store proc that does all of this for you or a combo of both, only you can figure out which is best for your process, and either can be triggered from an ASP page.