Replication

I have a customer who has Access databases at local sites who wants the data
replicated nightly to MS-SQL Server using broadband connection. 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"Ben" <Sorry@.TooManyViruses.com> wrote in message
news:6Eeic.14065$d7.7176@.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|||"Ben" <Sorry@.TooManyViruses.com> wrote in message
news:1Geic.14066$d7.8263@.twister.rdc-kc.rr.com...
> "Ben" <Sorry@.TooManyViruses.com> wrote in message
> news:6Eeic.14065$d7.7176@.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|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:4089b32d_2@.news.bluewin.ch...
> "Ben" <Sorry@.TooManyViruses.com> wrote in message
> news:1Geic.14066$d7.8263@.twister.rdc-kc.rr.com...
> > "Ben" <Sorry@.TooManyViruses.com> wrote in message
> > news:6Eeic.14065$d7.7176@.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

Thanks for the leads.|||I have more than 7 years of Experience with Replication if you want I can
Help u out in this. Please email me nehapatel75@.yahoo.com