Help: Table sync between online/offline databases

[mmcquade.forums]

Hey everyone,

Let me give you a rundown on our setup. We're a small, small firm, so
please take this setup with a grain of salt, with both of us knowing
it's probably not the best setup ever (and I'm more than happy to hear
suggestions).


On our webserver we have a MySQL database that collects information on
our clients (name, address, etc) when they fill out a form to sign up.


On our local network, we have an Access DB that's been split between
front and back end (with linked tables). The users of this database
know they cannot update the MEMBERS records on the local access
database (since the online one is considered the master)


Currently, through a series of copy and pastes, we will copy the
up-to-date record information from the online DB into the MEMBERS table

in the access table (exact same structure). While this system can be
cumbersome, it's working well and suits our needs.


In the pursuit of constantly making things "easier", I'd like to refine

this process. I'm currently playing around with ODBC and am able to
link the MySQL MEMBERS table into Access, which is nice because now
we're working with live data and don't need to manually update.


The problems I forsee, obviously, is speed since our net connection
isn't the fastest thing on the planet, so traversing the records is
slower. Also, if we should be without internet, we'd have no access to

our MEMBERS table.


Is there a way to store a local copy of the online table to be used..
sort of an "offline" version, then create a way to synchronize the
information between the 2?


I guess the long-and-short of it is that we're ok making updates and
changes to the online database, but if we could get the synchronizing
down to a button click, or something, that would be, at minimum, better

than what we have now.


If you were in this position, what kinds of things would you consider
doing?

Thanks!

Mike

 

[Tony Toews]

Let me give you a rundown on our setup. We're a small, small firm, so
please take this setup with a grain of salt, with both of us knowing
it's probably not the best setup ever (and I'm more than happy to hear
suggestions).

Decent setup and that's exactly what I'd do in your situation. And
thanks for a nice description of the environment and problem. Makes
life much easier for us.

The problems I forsee, obviously, is speed since our net connection
isn't the fastest thing on the planet, so traversing the records is
slower. Also, if we should be without internet, we'd have no access to
our MEMBERS table.

And you're thinking about all the problems. Fabulous.

Is there a way to store a local copy of the online table to be used..
sort of an "offline" version, then create a way to synchronize the
information between the 2?

Yes. You can use a DELETE query to clear all the records from your
local copy of the file and an APPEND query to copy all the records
down.

Hmm, now if'n I was doing that I'd copy all the records down into a
temporary table, make sure the record count was reasonable, ie same
number of records as last time or slightly larger, then I'd delete all
the records in the customer query and copy them from the temp table to
the customer table.

Also I'd record the date and time I did the successful record copy in
another table and display that on the main menu. This way folks will
know how old the data use.

Now you don't want to have any relationships with relational integrity
defined between your local copy of the customer table Otherwise
you'd never be able to delete your local copy. But that's fine too.

Something else you might want to consider doing is having an update
form bound to the mySQL table. If your Internet isn't working then
this form is going to get an error message when you open it. Oh well.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[mmcquade.forums]

Hi Tony,

Thank you for your replies (and encouragement). It's tough when you're
the only developer because you don't know if you're heading in the
wrong direction.

I will begin incorporating your ideas within the next month (it's
further down the task list), but it seems very doable.

Thanks again,

Mike

 

[Tony Toews]

Thank you for your replies (and encouragement). It's tough when you're
the only developer because you don't know if you're heading in the
wrong direction.

I will begin incorporating your ideas within the next month (it's
further down the task list), but it seems very doable.

Thanks again,

You're quite welcome. Best wishes.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm