Updating mySQL Database on Site

The database that drives my site is really created and maintained on a computer in Microsoft Access. I use mySQL's program "mySQL Migration Toolkit" to take the Access database and convert it into mySQL. After it is converted by "mySQL Migration Toolkit" I use a different program called "Core FTP" to grab the file off of the computer and upload the new mySQL file to my website's folders on Dreamhost so that it is technically on my site but not yet uploaded/useable by the site via the Dreamhost Goodies panel.

For the purpose of my question, let's call the database that is regularly updated and converted from Access "database1" and its single table "table1." Since I don't make a new Access database every time I want to update or add something, and my site's queries are dependent on the names "database1" and "table1", the name of the database and table do not change.

Can somebody possibly explain how I can overwrite the old copy of "database1" with the new copy of "database1"? I can see a button called "Modify" on the "Goodies" panel's "Manage mySQL" screen but I can never seem to figure it out. I just end up hitting the "Delete" button and then panic because my entire site crashes and I have to run for help to the support team to get any of the FTP'd databases back online.

If you could possibly also not assume I have a clue about things like "command line" it would also be a big help, I don't know what a command line is or where it comes from or even how to set one up. VERY basic instructions with beginning terminology are definitely most welcome and will be very much appreciated! :) Thanks in advance!

In reply to:
The database that drives my site is really created and maintained on a computer in Microsoft Access.

Sorry to hear that.

In reply to:
Can somebody possibly explain how I can overwrite the old copy of "database1" with the new copy of "database1"?

Rarely do I recommend software, but years ago in the process of migrating Access-based ColdFusion sites over to PHP/mySQL-based sites I came across this little gem:
http://www.dbtools.com.br/EN/order.php
The mySQL version is only $70US and includes tools that can both Import and Export MSAccess data. It can also perform quite a bit of data maintenance which can liberate you from MSAccess altogether if you so choose - for me it pretty much paid for itself after moving my first two clients. And you can deduct the cost from taxes at EOY as an business expense.

Note that in order to make it useful you will have to modify the DH database user config to allow connections from your remote host; frex, my home office system (currently) resolves to c-xxx-xxx-xxx-xxx.hsd1.pa.comcast.net (..where the x's represent my current IP address) so I have configured an administrative user with all permissions (and a very cryptic password) to allow connections from

%.hsd1.pa.comcast.net

You can also set it to only allow connections from c-xxx-xxx-xxx-xxx.hsd1.pa.comcast.net specifically for extra security but you'll have to either maintain the user record periodically or purchase a fixed IP address from whomever is your service provider.

In reply to:
"Note that in order to make it useful you will have to modify the DH database user config to allow connections from your remote host; frex, my home office system (currently) resolves to c-xxx-xxx-xxx-xxx.hsd1.pa.comcast.net (..where the x's represent my current IP address) so I have configured an administrative user with all permissions (and a very cryptic password) to allow connections from

%.hsd1.pa.comcast.net

You can also set it to only allow connections from c-xxx-xxx-xxx-xxx.hsd1.pa.comcast.net specifically for extra security but you'll have to either maintain the user record periodically or purchase a fixed IP address from whomever is your service provider."

This is exactly what I need to do - but exactly where in the MySQL User config do you do it?

Thanks for any help...

Cheers

Neil Atwood
Sydney, Oz

In reply to:
This is exactly what I need to do - but exactly where in the MySQL User config do you do it?

Neil, you reach the user configuration screen by navigating to the Control Panel->Goodies->Manage MySQL page, where you will find each of your MySQL servers listed, along with each server's host names, databases, and users. Click on the "linked" *user's name*, and you will be taken to a configuration screen for that user.

From there, I think you will clearly see where you can add IP addresses/domain that that user is allowed to connect from. Good Luck!

--rlparker

That's excellent!

Thanks for the step by step...

Cheers

Neil Atwood
Sydney, Oz

Updating mySQL Database on Site