MySQL Bugs: #14781: MS Access to MySQL doesn't convert DEFAULT values

Bug #14781	MS Access to MySQL doesn't convert DEFAULT values
Submitted:	9 Nov 2005 9:16	Modified:	5 Dec 2005 13:33
Reporter:	Michael Jensen	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S2 (Serious)
Version:	1.0.20rc	OS:	Windows (Windows XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
When I convert an MS Access database to MySQL, the default values are not converted at all.

How to repeat:
Convert an MS Access DB with the default settings used.

Thank you for the bug report.

Any expected date for a fix?

Please respond!

I need a fix asap.
If this will take long, could you please recommend another product which converts Access db's to MySQL (free product)

Please respond!

I need a fix asap.
If this will take long, could you please recommend another product which converts Access db's to MySQL (free product)

Due to limitation of MS Access the default values cannot be retrieved. If there would be a way we would be happy to add it to the tool.

Well of course there is. You can do it with this tool:
Access2MySQL Pro

Found this on the net, which is a VB.NET sample:
Dim cn As New OleDb.OleDbConnection
Dim schemaTable As DataTable
Dim i As Integer

'Connect to the Northwind MSAccess Database on local drive
cn.ConnectionString "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source='c:\Northwind.mdb'"

cn.Open()

'Retrieve schema information about tables.
'Because tables include tables, views, and other objects,
'restrict to just TABLE in the Object array of restrictions.
schemaTable = cn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Table s, _
New Object() {Nothing, Nothing, Nothing, "TABLE"})

'List the table name from each row in the schema table.
For i = 0 To schemaTable.Rows.Count - 1
Debug.WriteLine(schemaTable.Rows(i)!TABLE_NAME.ToS tring)
Next i

'Retrieve schema information about columns.
'Restrict to just the Employees TABLE.
schemaTable = cn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Colum ns, _
New Object() {Nothing, Nothing, "Employees", Nothing})

'List the column name from each row in the schema table.
For i = 0 To schemaTable.Rows.Count - 1
Debug.WriteLine(schemaTable.Rows(i)!COLUMN_NAME.To String)
Next i

'Explicitly close - don't wait on garbage collection.
cn.Close()
cn = nothing
schemaTable = nothing

Read more here:
http://www.pcreview.co.uk/forums/thread-1183447.php

The column is called "COLUMN_DEFAULT" in the colums table.

Michael,

thanks for doing the research. But our problem is that we use JDBC to access the MS Access database and there is no way to access metadata via OLE or VB scripts.

Therefore we can only fetch the metadata that MS Access offers via ODBC and they did not include the default value there. They are missing lots of stuff and some things can be selected out of the MSys* tables, but not all.

If you can use a tool from one of our partners for this, please go ahead.

Thanks,
Mike