"Ben" <So***@TooManyViruses.com> wrote in message
news:1G*****************@twister.rdc-kc.rr.com...
"Ben" <So***@TooManyViruses.com> wrote in message
news:6E*****************@twister.rdc-kc.rr.com... I have a customer who has Access databases at local sites who wants the data replicated nightly to MS-SQL Server using broadband connection.
Clarification: They want data stored in Access at many sites replicated to
a centralized MS-SQL Database via broadband connections (Cable, DSL, etc.).
I have very limited knowledge of best practices to accomplish this reliably.
Right now they are using some Java app that exports the Access data then
emails the files as CSV. The emails are then manually taken, copied
then imported. Very cumbersome process and VERY unreliable. There's gotaa
be a better way.
I want to recomend that they contract an expert to set this up but
really can't even say for certian what type of person they should look for at
this point.
Any ideas from a 40,000 foot perspective?
Thanks in advance.
- Ben
You might want to post in microsoft.public.sqlserver.replication for more
information, but I believe that Access is only supported as a subscriber
(receives data) not a publisher (sends data).
Alternatives would include setting up linked servers to the Access
databases, and pulling in the data with direct SELECT queries. Or use DTS to
create a generic data transfer package, which is parameterized based on the
Access DB location.
See "sp_addlinkedserver" and "Heterogeneous Subscribers" in Books Online for
more information.
Simon
