SQL Server - Access synchronization

[Scott McDaniel]

I have a VB app which stores information in an Access 2000 db. The VB app
handles multiple users (it's a logbook type of application, users share
lookup tables but don't share information among themselves - IOW, I couldn't
see your log entries and you couldn't see mine).

We're adding a web interface to the package - basically a scaled down data
entry interface to allow users to add log entries without being at their
workstations. The web version will be storing data in a SQL Server 2000 db.

I need to be able to synchronize data between the two - if a users
edits/adds a log entry in the desktop version, they need to be able to
synchronize the desktop and web (this is done via a button click, no
"realtime" updates). Same scenario with the web side, but the web interface
won't acutally be able to synch with the desktop - the user would have to
initiate the synch from their workstation. We've pretty much worked out the
synch logic path, and we're working on acutally interfacing with the SQL
Server.

Can this be done reliably using a standard ADO connection between the
desktop and the web site? I would imagine traffic would be somewhat
minimal - on average, no more than 100 records would be added or updated.
I've done some testing on this, and with 20 - 50 records it seems to work
fine both ways.

Suggestions of better/more reliable methods would be most appreciated, and
thanks for your time

[Simon Hayes]

I'm not sure what you mean by "reliably" - if the workstations connect
to the MSSQL server, then they're just another client, so they will be
as reliable as your network, application code etc allow. And a hundred
rows is a very small amount of data, unless perhaps you have to work
with text or image columns.

Personally, I would consider removing Access altogether, and use a
single MSSQL database - no sync required, one point of backup and
recovery, better security etc. But of course that may be a bigger
change than you're willing or able to make in your environment.

Simon

[Scott McDaniel]

Thanks for your reply. We considered moving totally to SQL Server, but users
will not always have Internet access. There are several text columns (none
over 500 characters) and no image columns, pretty much plain jane numeric
and text data.

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

I'm not sure what you mean by "reliably" - if the workstations connect
to the MSSQL server, then they're just another client, so they will be
as reliable as your network, application code etc allow. And a hundred
rows is a very small amount of data, unless perhaps you have to work
with text or image columns.

Personally, I would consider removing Access altogether, and use a
single MSSQL database - no sync required, one point of backup and
recovery, better security etc. But of course that may be a bigger
change than you're willing or able to make in your environment.

Simon

[Greg D. Moore \(Strider\)]

"Scott McDaniel" <sc***@infotrakkerDELETEME.com> wrote in message
news:D_********************@comcast.com...

Thanks for your reply. We considered moving totally to SQL Server, but users will not always have Internet access. There are several text columns (none
over 500 characters) and no image columns, pretty much plain jane numeric
and text data.
Look into them using the MSDE.


"Simon Hayes" <sq*@hayes.ch> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

I'm not sure what you mean by "reliably" - if the workstations connect
to the MSSQL server, then they're just another client, so they will be
as reliable as your network, application code etc allow. And a hundred
rows is a very small amount of data, unless perhaps you have to work
with text or image columns.

Personally, I would consider removing Access altogether, and use a
single MSSQL database - no sync required, one point of backup and
recovery, better security etc. But of course that may be a bigger
change than you're willing or able to make in your environment.

Simon

[Andy O'Neill]

"Scott McDaniel" <sc***@infotrakkerDELETEME.com> wrote in message
news:D_********************@comcast.com...

Thanks for your reply. We considered moving totally to SQL Server, but
users will not always have Internet access. There are several text columns
(none over 500 characters) and no image columns, pretty much plain jane
numeric and text data.
My inclination would be to write my own code to do the updating.
I'd store a last connection timestamp in the access database and a timestamp
on each change record.
Apply all updates >= last connection, update last connection....

The complication is if the users can stick a change in via the web interface
since their last connection on their laptop.
You'd need to keep a change log and apply this to the sql "master" database
in order of changes.
Not an issue if there's only one given user can change their own subset of
data and they only ever use the one laptop may or may not be connected.
Which is usually the case for salesmen.
I've worked on stuff where the salesmen would pull a local copy of their
data onto a laptop as they went off travelling. They then would make
changes as they were at client sites. Occaisionally, they get a chance to
connect remotely and push these changes to the main database.

--
Regards,
Andy O'Neill

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

I'm not sure what you mean by "reliably" - if the workstations connect
to the MSSQL server, then they're just another client, so they will be
as reliable as your network, application code etc allow. And a hundred
rows is a very small amount of data, unless perhaps you have to work
with text or image columns.

Personally, I would consider removing Access altogether, and use a
single MSSQL database - no sync required, one point of backup and
recovery, better security etc. But of course that may be a bigger
change than you're willing or able to make in your environment.

Simon

[Scott McDaniel]

Thanks Andy,

Your concerns were mine as well, and your scenario of the travelling
salesman is pretty apt. Users can only change their own records, and they
always do so through the same interface ... they can move the Access
database, or they can create a new Access database (for a new log) but that
will always be considered a "new" log, not an addition to an existing log
(users can "merge" logs together, at which time we would mark each new
merged record as a new record and add that to the web db).

Users can add/edit via the web interface, independent of the desktop
interface, but users cannot review/edit other users log entries - they are
not even presented in either the web or user interface. I was storing a
boolean value in a column, but like your idea of storing the last sych date
and comparing that to stored LastConnectionDate. I'm already timestamping
all updates/additions/deletes, so this would be simple to implement.
--
Scott McDaniel

"Andy O'Neill" <ao***************@lycos.co.uk> wrote in message
news:zk****************@fe3.news.blueyonder.co.uk. ..

"Scott McDaniel" <sc***@infotrakkerDELETEME.com> wrote in message
news:D_********************@comcast.com...

Thanks for your reply. We considered moving totally to SQL Server, but
users will not always have Internet access. There are several text
columns (none over 500 characters) and no image columns, pretty much
plain jane numeric and text data.

My inclination would be to write my own code to do the updating.
I'd store a last connection timestamp in the access database and a
timestamp on each change record.
Apply all updates >= last connection, update last connection....

The complication is if the users can stick a change in via the web
interface since their last connection on their laptop.
You'd need to keep a change log and apply this to the sql "master"
database in order of changes.
Not an issue if there's only one given user can change their own subset of
data and they only ever use the one laptop may or may not be connected.
Which is usually the case for salesmen.
I've worked on stuff where the salesmen would pull a local copy of their
data onto a laptop as they went off travelling. They then would make
changes as they were at client sites. Occaisionally, they get a chance to
connect remotely and push these changes to the main database.

--
Regards,
Andy O'Neill

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

I'm not sure what you mean by "reliably" - if the workstations connect
to the MSSQL server, then they're just another client, so they will be
as reliable as your network, application code etc allow. And a hundred
rows is a very small amount of data, unless perhaps you have to work
with text or image columns.

Personally, I would consider removing Access altogether, and use a
single MSSQL database - no sync required, one point of backup and
recovery, better security etc. But of course that may be a bigger
change than you're willing or able to make in your environment.

Simon