Re: Synching between Access and MSSQL
From: Trent (notvalid_at_dontspam.com)
Date: 11/03/04
- Next message: giddyup: "Exporting Access files to Excel"
- Previous message: DavidS: "setup question"
- In reply to: Reid: "Re: Synching between Access and MSSQL"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 3 Nov 2004 13:07:28 -0500
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. :)
"Reid" <warrex@cox.net> wrote in message
news:uxkO$jcwEHA.4040@TK2MSFTNGP11.phx.gbl...
> 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
>
>
> "Trent" <notvalid@dontspam.com> wrote in message
> news:uQUXSCbwEHA.2192@TK2MSFTNGP14.phx.gbl...
> > 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.
> >
> >
>
>
- Next message: giddyup: "Exporting Access files to Excel"
- Previous message: DavidS: "setup question"
- In reply to: Reid: "Re: Synching between Access and MSSQL"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
