Replication

[Ben]

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]

Re: Replication


"Ben" <Sorry@TooManyV iruses.com> wrote in message
news:6Eeic.1406 5$d7.7176@twist er.rdc-kc.rr.com...[color=blue]
> I have a customer who has Access databases at local sites who wants the[/color]
data[color=blue]
> replicated nightly to MS-SQL Server using broadband connection.[/color]

Clarification: They want data stored in Access at many sites replicated to a
centralized MS-SQL Database via broadband connections (Cable, DSL, etc.).
[color=blue]
> I have very limited knowledge of best practices to accomplish this[/color]
reliably.[color=blue]
>
> 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[/color]
a[color=blue]
> 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[/color]
this[color=blue]
> point.
>
> Any ideas from a 40,000 foot perspective?
>
> Thanks in advance.
>
> - Ben
>
>[/color]

[Simon Hayes]

Re: Replication


"Ben" <Sorry@TooManyV iruses.com> wrote in message
news:1Geic.1406 6$d7.8263@twist er.rdc-kc.rr.com...[color=blue]
>
> "Ben" <Sorry@TooManyV iruses.com> wrote in message
> news:6Eeic.1406 5$d7.7176@twist er.rdc-kc.rr.com...[color=green]
> > I have a customer who has Access databases at local sites who wants the[/color]
> data[color=green]
> > replicated nightly to MS-SQL Server using broadband connection.[/color]
>
> Clarification: They want data stored in Access at many sites replicated to[/color]
a[color=blue]
> centralized MS-SQL Database via broadband connections (Cable, DSL, etc.).
>[color=green]
> > I have very limited knowledge of best practices to accomplish this[/color]
> reliably.[color=green]
> >
> > 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[/color][/color]
then[color=blue][color=green]
> > imported. Very cumbersome process and VERY unreliable. There's gotaa[/color][/color]
be[color=blue]
> a[color=green]
> > better way.
> >
> > I want to recomend that they contract an expert to set this up but[/color][/color]
really[color=blue][color=green]
> > can't even say for certian what type of person they should look for at[/color]
> this[color=green]
> > point.
> >
> > Any ideas from a 40,000 foot perspective?
> >
> > Thanks in advance.
> >
> > - Ben
> >
> >[/color][/color]

You might want to post in microsoft.publi c.sqlserver.rep lication 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_addlinkedse rver" and "Heterogene ous Subscribers" in Books Online for
more information.

Simon

[Ben]

Re: Replication


"Simon Hayes" <sql@hayes.ch > wrote in message
news:4089b32d_2 @news.bluewin.c h...[color=blue]
>
> "Ben" <Sorry@TooManyV iruses.com> wrote in message
> news:1Geic.1406 6$d7.8263@twist er.rdc-kc.rr.com...[color=green]
> >
> > "Ben" <Sorry@TooManyV iruses.com> wrote in message
> > news:6Eeic.1406 5$d7.7176@twist er.rdc-kc.rr.com...[color=darkred]
> > > I have a customer who has Access databases at local sites who wants[/color][/color][/color]
the[color=blue][color=green]
> > data[color=darkred]
> > > replicated nightly to MS-SQL Server using broadband connection.[/color]
> >
> > Clarification: They want data stored in Access at many sites replicated[/color][/color]
to[color=blue]
> a[color=green]
> > centralized MS-SQL Database via broadband connections (Cable, DSL,[/color][/color]
etc.).[color=blue][color=green]
> >[color=darkred]
> > > I have very limited knowledge of best practices to accomplish this[/color]
> > reliably.[color=darkred]
> > >
> > > Right now they are using some Java app that exports the Access data[/color][/color][/color]
then[color=blue][color=green][color=darkred]
> > > emails the files as CSV. The emails are then manually taken, copied[/color][/color]
> then[color=green][color=darkred]
> > > imported. Very cumbersome process and VERY unreliable. There's gotaa[/color][/color]
> be[color=green]
> > a[color=darkred]
> > > better way.
> > >
> > > I want to recomend that they contract an expert to set this up but[/color][/color]
> really[color=green][color=darkred]
> > > can't even say for certian what type of person they should look for at[/color]
> > this[color=darkred]
> > > point.
> > >
> > > Any ideas from a 40,000 foot perspective?
> > >
> > > Thanks in advance.
> > >
> > > - Ben
> > >
> > >[/color][/color]
>
> You might want to post in microsoft.publi c.sqlserver.rep lication 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[/color]
to[color=blue]
> create a generic data transfer package, which is parameterized based on[/color]
the[color=blue]
> Access DB location.
>
> See "sp_addlinkedse rver" and "Heterogene ous Subscribers" in Books Online[/color]
for[color=blue]
> more information.
>
> Simon
>
>[/color]

Thanks for the leads.