CODEWITHSUNDEEP
phpmysqlms-access
Elitmiar
Elitmiar

Reputation: 36829

Importing MS ACCESS DB to mySql?

I'm working on a project atm, and I need to import data that is stored in a MS ACCESS database to mySql. For mySql I'm using phpMyAdmin on a Ubuntu machine, I have another Windows Machine where I can access the Access DB from, In MS Access 2003 I can't find an option to convert the data to mySql? Can this be done?

Upvotes: 7

Views: 28806

Answers (3)

tony gil
tony gil

Reputation: 9554

step by step guide to running Access frontend application with MySQL database on webserver (you dont need to IMPORT the tables, you can use your msaccess application WITH them on the webserver) and EXPORTING MsAccess tables to MySQL (once you start down that path, you want it to be a two-way road, believe me):

If you are running MsAccess, i suppose that you are using windows

  1. Install MySQL ODBC 5.1 Driver (connector) http://dev.mysql.com/downloads/connector/odbc/
  2. Open CONTROL PANEL on win machine
  3. ADMINISTRATIVE TOOLS (if Vista or Seven, search ODBC)
  4. SET UP DATA SOURCES ODBC
  5. SYSTEM DSN
  6. ADD

depending on your server, you might have some difficulty finding the server name or IP, look for SSH Database connection (or something like that). as an example, read NetSol's FAQ: http://www.networksolutions.com/support/how-to-back-up-the-mysql-database-using-ssh/

if you want to BATCH EXPORT / DUMP to MySQL from MsAccess, you can create a FORM in access, put a button on it, and in VBA create this sub for the OnClick() event:

Dim sTblNm As String
Dim sTypExprt As String
Dim sCnxnStr As String, vStTime As Variant
Dim db As Database, tbldef As DAO.TableDef

On Error GoTo ExportTbls_Error

sTypExprt = "ODBC Database"
sCnxnStr = "ODBC;DSN=DSNname;UID=userOnServer;PWD=pwdOnServer"
vStTime = Timer
Application.Echo False, "Visual Basic code is executing."

Set db = CurrentDb()

For Each tbldef In db.TableDefs
Debug.Print tbldef.Name
sTblNm = tbldef.Name
DoCmd.TransferDatabase acExport, sTypExprt, sCnxnStr, acTable, sTblNm, sTblNm
Next tbldef

MsgBox "Done!"
On Error GoTo 0
SmoothExit_ExportTbls:
Set db = Nothing
Application.Echo True
Exit Sub

ExportTbls_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExportTblsODST"
Resume SmoothExit_ExportTbls

sometimes, while running non-english windows you might get error 2507. change "ODBC Database" for "ODBC" (works with French).

IMPORTING: in MsAccess: 1. FILES 2. EXTERNAL DATA SOURCE 3. LINK TABLES

an MsAccess frontend doesnt really care what database engine it is using, so safe practice is to have 2 separate MDB's: queries, forms, macros, etc AND raw data. that way, you can seamlessly switch from using local database to remote server. and your core application file doesnt contain data proper.

Upvotes: 1

David-W-Fenton
David-W-Fenton

Reputation: 23067

It's always possible to do a quick and dirty export from Access to any ODBC database by selecting a table in Access and simply choosing EXPORT from the File menu. One of the export options (in the dropdown at the bottom) is ODBC, and if you have a DSN set up for your other database, you can export directly. Obviously, the data types won't necessarily be perfect for the target database, but it won't misconvert any data -- you just may need to tighten up the data types after the export.

I think it's astonishing that Access can do this, to be honest, but it works.

Upvotes: 2

Druid
Druid

Reputation: 6453

Take a look at Access to MySQL. Makes it easy to convert an Access database to MySQL.

Upvotes: 8

Related Questions