ID #1157

Migration of columns defaults from MS Access fails with SQLyog Import External Data Tool.

You experience that when importing data from Microsoft Access migration of columns defaults fail.

This is a confirmed issue and we believe it is a problem with the ODBC driver for Access. We tried the various driver versions shipped with different Windows versions and also the updated driver for Office 2007 here:

With neither of those drivers columns defaults are returned/exposed by the driver.

We also find that using Microsoft 'Data Transformation Services' for Microsoft SQL Server to migrate from Access to MS SQL Server column defaults are not migrated either.  So Microsoft has same problem themselves, it seems!

If you have a lot of column defaults and you do not want to add the column defaults manually after first import there is another workaround: export from Access (note the difference between 'export from Access' and 'import to MySQL' here) first time using the ODBC driver for MySQL ("MyODBC"). This can be done from Access file .. export menu.  Alternatively there are tools available that gives you an option to filter the export in various ways.  It is only required to export the database structure.  After that SQLyog Import External Data Tool can be configured and the advanced features of SJA Import External Data (like TRIGGERS, WHERE) can be used.

Tags: -

Related entries:

You can comment this FAQ

Comment of Don Lambert:
For all of you with the trial version please be aware that the Import External Data Tool does NOT come with the professional version. Only with Enterprise and Ultimate.
Added at: 2010-10-04 00:27

Comment of Peter Laursen:

it never was a secret. It is clearly documented here:

(and was for several years)
Added at: 2010-10-04 00:39