I have a SQL Server 2000 database as the back end for a
web-based application that is hosted at an off-site
colocation facility. I need the ability to create ad-hoc
reports for it. The people who will be creating the
reports are very familiar and comfortable using MS Access
to create reports. I could just set up a simple link in
the Access database to the remote SQL Server but I have a
feeling that is going to be very slow. (Access will
transfer all of the data across the wire) So my thought
was to create a way to export the SQL Server database to
Access on demand and then they could download the Access
database and create and run reports locally. They
wouldn't be "real time" reports but it would be close
enough. Is there a simple way to export a SQL Server
database to a blank Access database? I know I could
create a really complicated DTS package to do it but I
wondered if there was something simpler.

Thanks in advance.

Corey

Hi Corey,

You can try using Heterogeneous Replication.
To achieve this

1. Create a Publication of the database, that you want to replicate at the
offsite location.
2. Select Transactional replication (preferred) and for the subscriber
types select Heterogeneous Data Sources.
3. Select all the articles that you want to publish.
4. Create a DSN on the SQL server machine, pointing to the Access Database.
5. Make sure that the user account that you use to start the Agent Service
has approprivate rights to the Access Database.
6. Go to Configure Publisher, Distributors and Subscribers option.
7. Switch to the Subscribers tab
8. click on New
9. Select ODBC Data Source
10. Select the appropriate DSN Name
11. Enter Database USername, password; if any
12. Click on OK to close this dialog box
13. Switch to Create and Manage Publication.
14. Select the appropriate database and its publication name.
15. Click on Push Subscription
16. Click on Next
17. under the Managed Subscriber option you should be able to find the DSN
name, that you had added as the subscriber.
18. Let the subscription database name be the default
19. Set the frequency for updating the subscriber
20. Select the option for initializing the schema at the subscriber
21. Just click on Finish.

Thus you will create a transaction replication with an Access db as the
subscriber.

HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

v-ashrup (AT) online (DOT) microsoft.com (Ashish Ruparel [MSFT]) wrote in message news:<#VfF$Q#MEHA.3464 (AT) cpmsftngxa10 (DOT) phx.gbl>...

Thank you so much Ashish. I knew there had to be a simple way. This
is exactly what I was looking for!

Corey