Modify Database Problem!

[charon]

hi,

I'm using access to create my database instead of MySQL, this is because our site is hosting at web hosting company. Our main database is for keeping our property information which can be edited by our marketting people if there has any sales transaction, and this data will be reflected on the fly (our web-site).

Once there has new launching I need to modify the database by creating new table for the new property. Since the file size is big, so it takes time for me to download and uploading the
database.

What should I do when I download my database for modifying?? Our sale person might don't know that I'm doing modifying, and they might still update the database which is in the host. We have a lot of branches, and there has many sale person involve in updating the sales information of our property, so, there will be a problem for me if I need to call them up.

Please advice!!!

[sir pannels]

Hey,
hmm you sorta lost me.
Could you not just create a new database in the mysql db? so you have a db for your business info.. then create an interily new one for the rest or whatever.. am I missing why you cant?
-P-

[Dylan Leblanc]

It sounds like you guys need to hire a database administrator.

[raf]

of coarse, you could also learn some sql and generate the tables automaticaly when a new property was inserted.

i'm not sure i understand your problem, but downloading the db, modifying it and uploading it again, can't be the way to go.

[Jeewhizz]

You could use a lock on the table so no one can write to it till you're done, but its easier for all this stuff to be put in at the time of transaction...

jee

[Kiwi]

If I've understood the question properly, then the situation is this:
You have two copies on the database: one on-line and one locally.
You need to develop a synchronisation process to ensure that the two databases contain the same data.
The local database can be updated by anyone on your network (the sale's reps).
The on-line database can only be modified by you.
At present, you are down-loading the database, making the changes to it locally then uploading the modified database.

If all of this is correct, then you need to develop a better synchronisation process. While you might want to fully update the on-line database periodically, this is not what you want to be doing every day. Here's what I would suggest:
Don't copy the entire database. Work out what fields and records you need on-line and only include those in your on-line database (for example, don't include any sale completed records on-line). This will reduce the amount of data you need on-line. It will also reduce data duplication.
Develop a process to identify when a field has been updated in your local database. This should be a timstamp field in every table, that automatically is set to NOW() on any insert or update operation (I think you can trigger this in Access).
Add a table (or tables) to manage the synchronisation. This should record the date of the last synrchonisation, the tables and numbers of records effected. This will help to provide a roll-back in case of error. (This is a field that should be in every database table -- in every database design).
For a synchronisation, you need to find the records that have been updated since the synchronisation. Produce these records in as an update query that you can port to the mySql database (I would save these as a .sql file which you can upload to your server and execute). This could be with shell scripts, but that might not be necessary.
Develop a series of quick tests to make sure that the two databses are synchronised -- and how to handle exceptions.
Devise a schedule for the updates and stick to it.
You may wish to periodically overwrite the on-line database with a fresh one from the local database to clean out any synchronisation errors. Make sure you know how to do this.That's a basic synchronisation process.

[charon]

thanks for all the reply...
kiwi, I don't really catch what did you mean, my database knowledge is poor....
well, guess I might choose to use the most easier way, that is LOCK the database while I'm downloading my database for modification!!