Re: MS Access to MySQL - Database Conversion

Hello Nick Thanks for your extensive reply - but, unfortunately I am running Access 2000, and the database has been written in Access 2000 :-(. So I'm probably screwed. It's not a biggy if i am cause it's only a small database, so rewriting it wont be much of an issue. Cheers Tom ----- Original Message ----- From: Nick Talbott <nickt@powys.gov.uk> To: Tom Peck <tompeck@paradise.net.nz> Sent: Wednesday, July 05, 2000 8:22 PM Subject: Re: [PHP] MS Access to MySQL - Database Conversion > Hi, Tom > > I expect you'll get a lot of replies to this. Here's a copy of a reply I > posted in response to a similar query recently. As long as you're not using > Access 2000, all should work sweetly. > > (1) First, you need to install the MySQL ODBC driver on your PC. Get this > from your nearest MySQL mirror site. I suggest you install the > Microsoft ODBC 3 SDK as well (do this first) as this gives you some useful > utilities to diagnose the connection in case of problems. > > (2) Now, configure an ODBC data source to point to the MySQL server and > database you want to use. Many ways of getting into this, but the simplest > is to open the Windows Control Panel, and fire up the ODBC manager. Fairly > straightforwards. Sequence is > > Add... > > Select MySQL > > Fill in the driver configuration box. The Windows DSN name is your choice > of logical name for this ODBC link. Fill in other fields appropriately (you > can leave the SQL command on connect blank). For use with MS Access 7, > check the second tick-box titled "Return Matching Rows". (For other > options, see the readme in the ODBC zip file.) > > (3) Now, run Access and load the customer's database. For each table you > want to export, follow this sequence ... > > select the table (Tables tab) > > File|Save As/Export... > > To an external file or database [OK] > > in the following dialog box, set the drop-down menu "Save as type" to ODBC > Databases and confirm or change the table name [OK] > > choose the ODBC source as the logical name you set up in step 2 above [OK] > > That's it! > > - - - > > You can also edit the data live on the MySQL server using Access. Open a > new database (you'll have to give it a dummy name on the local machine) and > then use the sequence ... > > File|Get external data... > > choose Link tables ... for live update on the remote MySQL database > > again choose ODBC Databases in the drop-down "Files of type" menu > > select your ODBC MySQL source > > choose the table to work with, and select an appropriate key field to use > when updating the database > > - - - > > Potential pitfalls: > > Some field types that Access supports don't map via ODBC. You may have to > amend the Access definition slighlty before the export process will work > smoothly. Things that both support - like auto-increment fields - can not > be communicated using ODBC. So Access exports an autoincrement field as an > integer. To put it right, you need to redefine the field in MySQL after > you've exported it. > > > HTH - Nick > > Nick Talbott > IT Policy and Strategy Manager, Powys County Council, UK > > email nickt@powys.gov.uk > FAX +44 (0) 1597 824781 > web http://www.powys.gov.uk and > http://www.powysweb.co.uk > > -----Original Message----- > From: Tom Peck <tompeck@paradise.net.nz> > To: php-general@lists.php.net <php-general@lists.php.net> > Date: 05 July 2000 04:25 > Subject: [PHP] MS Access to MySQL - Database Conversion > > > Hello everyone > > I have a site which uses a Microsoft Access database, connected through > ODBC. This is currently hosted on a Windows NT Machine, without problems. > Now it needs to be ported to a Linux machine - and a MySQL database. > > My Question: > > What is the easiest way to convert the data from one database to the next? > Secondly - I haven't dealt with MySQL before - how much of the code will > need to be changed for it to work with the MySQL database? > > If there's too much work involved, I may stick with the Access database. > How can this be setup on the linux machine? It it was possible - would the > code need to be changed? > > Thanks everyone - I look forward to your reply's. > > Tom Peck > > > >