Page 1 of 1

MS Access to MySQL Conversion Problems

Posted: Sat Nov 22, 2003 2:52 pm
by vinyl-junkie
Hi all,

This is my first post to your forums. I did a search of the archives and didn't find an answer to my question, so I hope someone can help.

I'm trying to use some software that I purchased from convert-in.com to convert a MS Access database to MySQL. The conversion software works great on one of my websites that has a Windows based server. However, I'm having trouble getting it work with a site that I just moved to a Unix based server.

Before I go into detail about just what specific problem I'm having, is there anyone here who is familiar with and uses this software? I'd sure like some help with what I might be doing wrong. I have contacted both my web host and the company I bought the software from, and neither knows what the other needs to be able to answer my questions.

Posted: Sat Nov 22, 2003 2:58 pm
by thomasd1
typical for microsoft :?
sorry m8 i can't help :oops:

Posted: Sat Nov 22, 2003 9:04 pm
by infolock
This may be just what you are looking for. Found it at
http://www.kitebird.com/articles/access-migrate.html

In other words, I don't think you are gonna need that application at all that you have downloaded since Access has built in features for exporting the DB. Here is the steps the site above suggests :
One approach to migrating data from Access to MySQL is to use the export feature provided by Access itself to write out the contents of each table as a text file. Each file then can be loaded into MySQL using a LOAD DATA statement or the mysqlimport command-line utility. Suppose you export a table mytable into a file mytable.txt using CSV (comma separated values) format, and you want to import it into a table named mytable in a MySQL database named mydb. You can invoke the mysql program, then issue a LOAD DATA statement to import the file like this:


C:\> mysql mydb
mysql> LOAD DATA LOCAL INFILE 'mytable.txt'
-> INTO TABLE mytable
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n';

Alternatively, use mysqlimport from the command line (type the command all on one line):

C:\> mysqlimport --local --fields-terminated-by=,
--fields-enclosed-by='"'
--lines-terminated-by='\r\n'
mydb mytable.txt

If you need to provide connection parameters such as the hostname, user name, or password, list them on the mysql or mysqlimport command line before the database name:

C:\> mysqlimport --local --fields-terminated-by=,
--fields-enclosed-by='"'
--lines-terminated-by='\r\n'
-h some_host -p -u some_user
mydb mytable.txt

The advantage of this approach is that it requires no special conversion tools. It can be used to produce data files even on machines that have no MySQL support. (If you don't have the MySQL client programs installed on your Access machine, create the data files, then copy them to another machine where the MySQL programs are installed and load the files into MySQL from there.) The disadvantage is that the MySQL tables must already exist before you can load data into them, so you must issue the appropriate CREATE TABLE statements yourself. For the example just shown, that means you must already have created the table mytable in the mydb database before using either LOAD DATA or mysqlimport.

Converters That Generate Intermediate Files


A second approach to data transfer is to use a converter that reads an Access table and produces from it one or more files containing SQL statements that create the table for you and load data into it. Then you execute the intermediate SQL file or files using the mysql program. Several free converters that work like this are available, each of which takes the form of an Access module:


exportsql.txt
Works with Access95, Access97, Access2000. Exports all tables in a database, producing one file containing DROP TABLE statements (in case you want to remove MySQL tables created during an earlier data transfer exercise) and another file containing CREATE TABLE and INSERT statements for creating and loading the tables. The files are written to the C:\TEMP directory.

access_to_mysql.txt
Exports all tables in a database into a file C:\TEMP\mysqldump.txt containing DROP TABLE, CREATE TABLE, and INSERT statements to drop any existing MySQL tables and recreate them. Less sophisticated than exportsql.txt in terms of type conversion and handling of special characters.

mdb2sql.bas
Access97 only. Exports selected tables to files in a directory of your choosing. Writes a data file for each selected table, plus one SQL script containing CREATE TABLE statements for creating the tables and LOAD DATA statements for importing the data files into them.

Near the beginning of the source code for each of these converters, you'll find instructions that you should read, because the details of the process for generating the SQL and data files are converter-specific. Also, be sure to note any prerequisites that must be satisfied before using the converters. These include the following:

Both exportsql.txt and access_to_mysql.txt expect to write files to the C:\TEMP directory, so you must create that directory if it doesn't exist:

C:\> mkdir C:\TEMP

Alternatively, you can modify the module source so that it writes files to another existing directory.

If you want to use exportsql.txt to convert Access2000 tables, you need to enable support for the DAO (Data Access Objects) interface. From Access, go into the Visual Basic editor, select the Tools >> References menu option, then enable the "Microsoft DAO 3.6 Object Library" option in the window that comes up.

mdb2sql.bas requires that you have Advanced Wizards installed, because it uses the Documenter function included in that Wizard set.

After following the export procedure for a converter that generates intermediate SQL files from Access tables, you'll end up with one or more files that need to be executed with the mysql program, as follows. Assuming that you want to create tables in a database named mydb, you can execute a SQL file file.sql like this:

C:\> mysql mydb < file.sql

If you need to provide connection parameters, list them on the command line before the database name:

C:\> mysql -h some_host -p -u some_user mydb < file.sql

Converters That Perform Direct Data Transfer


Some conversion tools can transfer data directly from an Access database into MySQL. That is, they create the MySQL tables for you and load the information into them as well. This avoids the need for any intermediate files. On the other hand, such tools require that you be able to connect to the MySQL server from the machine on which your Access information is stored. (This requirement is easily satisfied if you install MySQL on your Access machine.)


Tools that can perform direct data transfer are:


MyAccess
$30 shareware. (Non-registered copies are fully functional, but an annoyance dialog that must be dismissed pops up every five minutes.) Works with Access97, Access2000. MyAccess is an Access add-in that allows direct transfer when you connect from Access to MySQL over an ODBC connection.

DBTools
Free. Works with Access97, Access2000. DBTools actually is intended primarily as an application for administering MySQL, but it includes data import capabilities that can be used to read Access databases for transfer to MySQL. (It can also read data from other sources such as Excel spreadsheets, making it particularly useful for transferring to MySQL information that is stored in a variety of formats.) Because DBTools reads Access databases directly, you can use it to migrate Access tables even if you don't have Access installed locally, as long as you have the database files containing the tables to be transferred. DBTools does not require ODBC.

MySQLFront
Free. MySQLFront is similar in many ways to DBTools. It can read Access97 and Access2000 files directly. If ODBC is installed, MySQLFront can import information into MySQL from ODBC data sources over the network. (Unfortunately, MySQLFront development has ceased and it is no longer distributed by its author. However, you may be able to find it on alternate download sites by using a search engine.)

As an example how one of these tools works, here's how you'd use DBTools to perform data transfer from Access to MySQL. Begin by visiting the DBTools download page at http://www.dbtools.com.br/, transferring the installer (a program named setup.exe), and running it. This will install DBTools on your machine.

If you want to transfer Access2000 databases, you need to enable DAO. (If you don't, DBTools will crash whenever you try to open an Access2000 database.) To turn on DAO, launch DBTools (it will tell you there is no server profile; that's normal), select the Options >> Preferences menu item, and select the DAO 3.6 option. Then quit and relaunch DBTools, because DAO isn't actually activated until the next launch after you enable it.


With DBTools running, establish a connection to your MySQL server. (Click the Server icon in the toolbar or use the Server >> Add Server menu item to define a profile for the MySQL server you want to connect to.) You must be connected to the server before you can transfer information; many of the menu items and icons in the tool bar are disabled until you establish a connection, including those related to importing data.


After connecting to MySQL, use the Import Data Wizard to select the Access database file containing the tables you want to transfer. One of the dialogs presented during this process asks you to select the file type for the kind of database you want to use. Select the Access97 type for either Access97 or Access2000 databases.


If you intend to continue using Access after transferring the tables, open the database from Access, delete the tables that you just transferred to MySQL, connect to the MySQL server, and set up links to the tables.
hope that helps.

Posted: Sun Nov 23, 2003 12:44 am
by vinyl-junkie
Thanks for the response. Great site that you referenced. From everything I've looked at and tried to do, it looks like a do-it-yourself approach like you've given is going to be my only choice. I've been told that the software I'm trying to use just isn't going to work with my web host. :(

I've managed to successfully create one of the tables (tblArtists) that I will be using for my database (napathon_cdtrust). Now I am trying to load that table with my csv file (tblArtists.csv), but I'm getting an error. This is being executed from a PHPMyAdmin query window. Here's my code:

Code: Select all

mysqlimport --local --fields-terminated-by=, --fields-enclosed-by=" --lines-terminated-by=\r\n 

napathon_cdtrust tblArtists.csv ;
Here's the error:

Code: Select all

Unclosed quote @ 66
I think that it's barking at the double quote but I have no idea how to correct the code.

BTW, I haven't mentioned this before but I'm a PHP/MySQL newbie, so if this is a dumb error that a more seasoned coder should catch, please forgive me.

Posted: Sun Nov 23, 2003 2:08 am
by infolock
as far as converting an access db TO mysql, yeah you pretty much have to do it yourself ( there might be a way to just send the database straight over to mysql and it create the tables/databases for you on the fly, but i've not seen it ). sucks, but at least you get the experience not many do :P

but to the current problem, are you issuing that command from the command prompt or from within your code?

because there is a much better way to send data from a CSV file to a mysql database, and that's through php ! i'm gonna refer you to another site, but that's because there are so many better versions of how to do that then what i could give you.

there are actually two. both of these contain scripts people have made to do just what you are asking with CSV files :

http://www.evilwalrus.com
http://www.hotscripts.com

just search their code archives and you'll find all kinds of solutions to this :)

hope that helps.

Posted: Sun Nov 23, 2003 3:02 am
by vinyl-junkie
infolock wrote:but to the current problem, are you issuing that command from the command prompt or from within your code?


I'm doing it from a command prompt.
because there is a much better way to send data from a CSV file to a mysql database, and that's through php !
Right, but - and here's where I reveal my true ignorance - isn't what I'm doing from the command prompt something that I could also execute through a PHP web page? That's something that I will eventually setup, but for now I'd just like to get the database setup on the server any way I can.
http://www.evilwalrus.com
http://www.hotscripts.com

just search their code archives and you'll find all kinds of solutions to this :)
I found a couple of applications at the hotscripts.com website. Not sure how you find that sort of thing at evilwalrus - nothing showed up when I searched there. :? Anyway, I think choosing some other software will probably end me up exactly where I am right now. My web host will not let me logon to the MySQL server remotely, so unless the software can run on my website, I'm screwed. I already tried another one, MySQL-Front, and ran into that same issue. *sigh*

Could you take a look at that SQL statement I got the error on and tell me what I'm doing wrong? I'd really appreciate it.

Posted: Sun Nov 23, 2003 3:32 am
by infolock
My web host will not let me logon to the MySQL server remotely, so unless the software can run on my website, I'm screwed.
that's what php does, is run on your website. the links i gave you wasn't for software, it was for php code, that lets you access MySQL from wherever you specify. unfortunately, i'm not sure about the command itself though. I've never had to do it from a command prompt.

but let's backup just a few. You are saying that they won't let you connect to MySQL remotely. Do you mean you are gonna have your php scripts on one host, and your database on another? if that's the case, then why even put that info into mysql in the first place?

anyways, i thought evilwalrus had some, but i checked myself and saw none. so to compensate for my stupdity, here are some links directly to cvs to mysql scripts:


http://codewalkers.com/seecode/175.html
http://www.scriptsearch.com/details/6497.html
http://www.planet-source-code.com/vb/sc ... 8&lngWId=8
http://www.planet-source-code.com/vb/sc ... 5&lngWId=8
http://www.hotscripts.com/Detailed/18098.html

Posted: Sun Nov 23, 2003 9:57 am
by vinyl-junkie
that's what php does, is run on your website. the links i gave you wasn't for software, it was for php code, that lets you access MySQL from wherever you specify.
Yes, I know that PHP runs on my website. I've already written a little bit of it already, not much and my first script isn't totally functional, but I digress.

I think we're starting now to talk about two different things though - converting my MS Access database to MySQL, and creating dynamic content web pages that read the MySQL database. I know that PHP will do both, and that I will likely write a PHP script that refreshes my MySQL database whenever I change it. For now though, I'm finding the command prompt useful in helping to point out the bugs in my code.
unfortunately, i'm not sure about the command itself though. I've never had to do it from a command prompt.
No problem. I know of a guru in another forum who I'm sure will be able to help with that one. I'll post a message there.
but let's backup just a few. You are saying that they won't let you connect to MySQL remotely. Do you mean you are gonna have your php scripts on one host, and your database on another? if that's the case, then why even put that info into mysql in the first place?
I can see I haven't been at all clear with what I'm doing. This may be more than you really wanted to know but here goes. I have some purchased software, CD Trustee, that I run on my desktop which maintains a database for my music collection. One of its features is to lookup new database entries in something called the CDDB and automatically populate the database. CD Trustee has MS Access as a back end, and it's that back end database that I'm wanting to convert to MySQL and write some scripts to create web pages from.

Bottom line is this: The Access database needs to stay on the desktop; it isn't going away any time soon.

It should be noted at this point that I'm fully aware that I could use the MS Access database as is with some ASP scripts. I'm already doing that. You might be asking yourself at this point why I want to convert everything over to PHP/MySQL. Several reasons - (1) I want to learn it, (2) I have free ASP scripts on my website for the MS Access version, and I want to provide an equivalent for PHP/MySQL, (3) I want to move my main website to a Unix based web host. I won't be able to use my ASP scripts there if I do. Hopefully, this explains where I'm coming from.
here are some links directly to cvs to mysql scripts:
Thanks for those links. I've only looked at the first one so far, but it sounds like it will do exactly what I'm looking for. I'll post back and let you know which one I decided to go with. Thanks for the help.

Once I get this database converted, I'm going to need some serious help in getting my PHP code working. I've been reading a section in one of my books about object oriented programming. Sounds interesting but it's going to represent a shift in thinking from the way I've approached coding in the past. I'm hoping you guys can help me with that. :)

Posted: Sun Nov 23, 2003 10:04 am
by vinyl-junkie
I just re-read that already too long post of mine, and I can see that there are still some gaps in the explanation about logging onto the MySQL server remotely.

The software that I was talking about using for the purpose of converting my database runs on my desktop, taking the MS Access database on my desktop and converting it directly on the server. Obviously, that means that I have to logon to the server from my desktop, which as I stated my web host won't let me do.

Did I do a better job of explaining it here, or did I dig myself in a little deeper? :wink:

Posted: Sun Nov 23, 2003 12:02 pm
by vinyl-junkie
I just tried this conversion tool. In a word - sweet! The only thing that I didn't care for was the fact that the tables had to already be defined on the server before I could run the code. Still, that's a one-time thing that I don't think I'd ever have to do again, unless for some reason I needed to delete and re-create a table. That would be easy enough to do.

Thanks for the recommended sites! I had done a search myself but I guess I wasn't searching for the right thing. Anyway, this is ging to work like a charm for me. :D