Is it possible to create a Dump of SQL commands from a Microsoft Access database? I hope to convert this MDB file into a MySQL database for importing so I don't have to go through the CSV step.
I would expect even an MSSQL dump file to still contain workable SQL commands, but I know nothing of MSSQL, please let me know.
Right click on the table you want to export, and in the menu that appears, choose Export , ODBC Database. The Export dialog box appears. Enter the desired name for the table after its import into the MySQL server, and click OK.
Click File, click Save As, and then click Save Database As. Choose the Access file type, and then click Save As. If any database objects are open when you click Save As, Access prompts you to close them prior to creating the copy. Click Yes to make Access close the objects, or click No to cancel the entire process.
You want to convert mdb to mysql (direct transfer to mysql or mysql dump)?
Try a software called Access to MySQL.
Access to MySQL is a small program that will convert Microsoft Access Databases to MySQL.
- Wizard interface.
- Transfer data directly from one server to another.
- Create a dump file.
- Select tables to transfer.
- Select fields to transfer.
- Transfer password protected databases.
- Supports both shared security and user-level security.
- Optional transfer of indexes.
- Optional transfer of records.
- Optional transfer of default values in field definitions.
- Identifies and transfers auto number field types.
- Command line interface.
- Easy install, uninstall and upgrade.
See the aforementioned link for a step-by-step tutorial with screenshots.
If you have access to a linux box with mdbtools installed, you can use this Bash shell script (save as mdbconvert.sh):
#!/bin/bash TABLES=$(mdb-tables -1 $1) MUSER="root" MPASS="yourpassword" MDB="$2" MYSQL=$(which mysql) for t in $TABLES do $MYSQL -u $MUSER -p$MPASS $MDB -e "DROP TABLE IF EXISTS $t" done mdb-schema $1 mysql | $MYSQL -u $MUSER -p$MPASS $MDB for t in $TABLES do mdb-export -D '%Y-%m-%d %H:%M:%S' -I mysql $1 $t | $MYSQL -u $MUSER -p$MPASS $MDB done
To invoke it simply call it like this:
./mdbconvert.sh accessfile.mdb mysqldatabasename
It will import all tables and all data.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With