Re: SQL Server - Access synchronization

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" <aon14nocannedmeat@xxxxxxxxxxx> wrote in message
news:zkGNe.6678$jr4.172@xxxxxxxxxxxxxxxxxxxxxxxxxxxx
> "Scott McDaniel" <scott@xxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:D_-dneH90r1TQJjeRVn-3w@xxxxxxxxxxxxxx
>> 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" <sql@xxxxxxxx> wrote in message
>> news:1124456267.588573.89760@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>>> 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
>>>
>>
>>
>
>


.

Relevant Pages

  • Re: eval MySQL commands
    ... >> abstract DB connection class and implement this interface for each real ... >If I define objects for every type of database connection, ... Yes - you define the interface in the base class, ... MySQL subclass, or maybe the MS SQL one. ...
    (comp.lang.php)
  • Re: My First C# (warning - long post)
    ... if it contains the keyword. ... from hitting the database to determine if the name is a database name. ... KeywordDictionary interface. ... push the knowledge of the Connection to the data source class ...
    (comp.lang.cobol)
  • Re: Singletons in PHP
    ... Passing the necessary related objects through the interface (methods, ... Suppose you have an object that references the Singleton "Database". ... giving that interface a param, like a connection string, would ...
    (comp.lang.php)
  • Re: APQ (Connection Cloning)
    ... > "why would you need more than one connection to the very same database ... In the Oracle Call Interface for example you may call a statement ...
    (comp.lang.ada)
  • Re: Beginning C# Q
    ... starting out with a network app0lication, you have an awful lot to swallow. ... Designing your database is therefore, not quite the first step, particularly ... Groups table, which defines which Groups users belong to, and a Permissions ... That is why an Interface is called an Interface. ...
    (microsoft.public.dotnet.framework)