uploading a whole table of data

[Homer]

I run an on-line database of companies that users can use free of charge to find information about products or services in their area, there are currently approx 5,000 companies included. All the information is initially entered on to a database in Access on my local PC and any changes or updates are carried out here, although I can also update each record individually one at a time via an on-line form. The problem I have is that updating each record individually on-line takes a lot of time and I may need to update about 150 to 200 records a day. The way I have overcome this is to update the local database and then once every 7 - 10 days send a copy of this file to the administrator(who is also the host) who then(maybe after another 4 or 5 days or whenever he feels like it) updates the site directly on the server. I believe he converts the data to MySQL, the scripts are written in Perl. This of course is a very inefficient and expensive way of doing things and can sometimes cause problems with the companies involved.

Is it not possible to have a script whereby I could upload the whole table, or even just the updates to the SQL file and overwrite the old data.

In my narrow perspective of things it would seem an ideal solution but I am sure it is not that easy, so if there are wizards out there I would like to hear from them.

Regards

Homer

Oh! and by the way I need to keep a local database because this is used for other purposes such as mailshots, stats etc.

[Kiwi]

OK. From an architeture point of view, there's a bunch of problems here. With data duplication, you're running the risk of all sorts of potential problems unless you have a proper synchronisation process.

That said there is probably something that can be done to improve it.

Firstly: do you have access to run SQL on your mySql database? If you do, then you could use access to generate a report that would simply be the update/insert queries for the database. This wouldn't be too hard to do and would allow you to keep track of things.

Ideally, this would be systematised (when I build a database, every-row has a 'last-update' field -- with a time-stamp in it. If you have something like this, then you can select on this field, quickly find all changes since a certain date, and replicate the tables).

A better solution (from a data architecture point of view) would be to maintain a proper dual-system. Here you would have a local database that is identical (both structure and data) to your server database. You would then also have a working database -- where you make your changes.

You could run this in several different ways -- but the simplest would be to do something like:
1. Update the working database real-time. This may contain more data than the server database.
2. Periodically (daily, weekly or monthly) update the server database and the local mirror.
4. Work out some sort of validation checks between the local mirror and the server database. This will involve things like number of records in tables, column sums, first, nth and and last row comparison and use these to validate the two systems. Identify and fix any discrepancies asap.
4. After the update, run a more detailed comparison check between the local mirror and the working database. Because they're both local, this can usually be done fairly quickly.

This is a fairly standard synchronisation architecture. Unless you have "proper" automated synchronisation, then this is likely to be the best solution.

[Spookster]

Setup phpMyAdmin on your host and configure it with your mysql database. Export the data and structure of your access database into a comma delimited file. Log into your phpMyAdmin then import the file.

[Spookster]

Of course it would be wise to setup a php/mysql development environment on your local system and practice there first.