Synching between Access and MSSQL

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Trent - 03 Nov 2004 15:23 GMT

Hi everyone,
I am looking to create an MSSQL database that has an Access 2000 front end.
The client side Access app needs to download selected bits of data from the
SQL database and the clients would work offline with this data and later
when they re-connect the data would then need to be synchronized back into
the SQL database.

Can anyone point me in the right direction to get started with this so I do
not have to re-invent it all?
My code will be significantly more complex eventually to accomodate how I
need to manipulate and validate data but if someone can get me started with
info on the best method for 1. grabbing specific data from the SQL database
directly into Access programmatically, and 2. Programmatically connecting
back to the SQL database from Access to send back the changes, then I would
have a great place to build from.

Any sample code for connecting to external databases?
Preferably, the Access app should be able to test if it has access to the
database first and then respond by either uploading the data if connected or
alternatively storing the data locally. I will have to setup flags in
the data to monitor status and do comparitive checks to make sure it went
through without a problem before altering local flags, etc.

Thanks for any help.

Reply to this Message

Reid - 03 Nov 2004 18:17 GMT

Look at SQL Server replication. There are 3 types of replication - look
especially at "Transactional with Queued Updates" - it was new with SQL
Server 2000.

How long will the clients be disconnected, i.e. max number of days without
synching up? One of the replication parameters is specifying how many hrs
or days to cache data before disabling a subscription. I've resynched as
much as 10 days' worth. I was also burned - once - by accepting the default
(3 days, I think) and then not being able to resync beyond that.

I've always done replication SQL-to-SQL, native mode, but it supports 2 way
SQL-to-Access as well. You might consider MSDE on the Access client, since
native mode is faster and presumably more reliable.

Reid

> Hi everyone,
> I am looking to create an MSSQL database that has an Access 2000 front end.
[quoted text clipped - 20 lines]
>
> Thanks for any help.

Reply to this Message

Trent - 03 Nov 2004 19:07 GMT

The clients will only be disconnected a matter of a few days in most cases
and most of the time will likely have the ability to connect to the master
database anyway.
What I am doing is creating an Itinerary application so that our Event
Planning folks can do itinerary planning for VIPs that go to off site
events.
The Event Planners travel to the event and maintain the daily itinerary for
each person. Each night they will print out the next day's itinerary for
each person and slip it under the door to their hotel room.

The challenge is that the information has to be tracked in a main database
which will likely be MSSQL2000, the clients (here meaning Event Planners)
will download data for the customer they are assigned and be able to work
with that data while disconnected from the main database.
When re-connecting to the database any changes they have made will need to
be synchronized back to the main database.
To make things a bit more complicated I have to account for the possibility
that a customer might need to be reassigned to a different planner so I have
to maintain info on assignation of the client in the main database and
accomodate those changes if the original assignee synchs back with the
database again.

Working out the details of how to keep the data synchronized and accomodate
for emergency changes of assignation is going to get a bit complex but I
think I will be able to figure it out.

In the meantime my Access programming has been very limited and I do not
know how to make connections to external databases.
I found code to allow an ADO connection to a local Access database but now I
have to figure out how to make a connection and retrieve data from an MSSQL
database. I have not found any code samples for doing it yet. That is
probably the biggest roadblock I have at the moment.
I did not think that making direct links to the tables would be the best
method for an app that will only connect on occasion.

I have never used replication and will have to look into it. Given the extr
a testing that I have to do to accomodate reallocation of client data though
it might not work for me. I might have to do it all manually. Also, some
of the tables will be created dynamically so what data I need to pull down
to the client will have to be determined by a query to one of the main
controlling tables.
Seems like a lot of effort for the end result. :)

> Look at SQL Server replication. There are 3 types of replication - look
> especially at "Transactional with Queued Updates" - it was new with SQL
[quoted text clipped - 43 lines]
> >
> > Thanks for any help.

Reply to this Message