Search code examples
mysqlsqlms-accessmdbtools

Converting access db to sql using mdbtools

While I was trying to import my .accdb to sql database by using command

mdb-schema IPEDS201617.accdb | mysql -u root -p IPED201617

It is throwing an error which says

ERROR 1064 (42000) at line 11: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[ADM2016]
(
    [UNITID]            Long Integer,
    [ADMCON1]            Integer,
    [ADMCON2]            Inte' at line 1

I thought it is a datatype error so I converted all long integer to text but the error still persists.

I simply want to get SQL DB from .accdb So if there is any way to do that directly.**

Solution 
  • % mdb-schema db.accdb mysql > schema.sql

% mdb-tables -1 db.accdb | xargs -I {} -n 1 mdb-export -D '%Y-%m-%d %H:%M:%S' -I mysql db.accdb {} > data.sql

    This will create Schema and data sql files seprately.