Export view data programmatically in Access/SQL Server

Question

We have an Access application front-end connected to a SQL Server 2000 database. We would like to be able to programmatically export the results of some views to whatever format we can (ideally Excel, but CSV / tab delimited is fine). Up until now we've just hit F11, opened up the view, and hit File->Save As, but we're starting to get results with more than 16,000 results, which can't be exported.

I'd like some sort of server side stored procedure we can trigger that will do this. I'm aware of the sp_makewebtask procedure that does this, however it requires administrative rights on the server, and for obvious reasons we can't give that to everyone.

Any ideas?

Answer 1

If you wanted to do everything in access you could link the view as a linked table and then using the TransferSpreadsheet method you could export that “table” as a csv file

EDIT:

As you want to do it server side check this out

http://www.mssqltips.com/tip.asp?tip=1633

I have used this before and it worked just fine

Answer 2

You may want to look at SSIS - it allows creating server side packages to export data on server side.

Another option is to right-click your database and run through data export wizard (which is using SSIS underneath).

Yet another option is to create command line utility (SQLCMD) to export the data into a flat file.

Answer 3

Have you used VB or Macros?

1. Create a local table the looks like the view structure
2. create a query that deletes the contents of the table
3. create a query that inserts the contents of the view to the local table
4. use the "analyze with excel" feature or one of the built in export features
5. Create a macro (or vba) that runs the first two qrys and the export with a single click

I just tried it with 26k rows and it worked without a problem

HTH