Problem In Importing Access Table

[knowledgenotebk]

Hi,

I attempted to import an Access database to mySQL (5.5).

Following the steps listed at geekengine, URL,

http://www.geeksengine.com/article/export-access-to-mysql.html

It bombed out at Step 7, that is, Access table failed to be imported into mySQL (5.5) server. The err msg is:

ODBC Failure. “The specified DSN contains an architecture mismatch between the Driver and Application”.

Additional info:

The mySQL ODBC driver = Version 5.01.09.00 (from Oracle)

WEbyog = Version 9.33 (trial version)

Thoughts, better, solution would be appreciated.

[peterlaursen]

To import from Access you will not need the 'myODBC' driver, but only the driver for Access/.mdb (that ships with Windows). SQLyog will not need a driver to connect to MySQL – but needs it in order to connect to Access. Actually in the wizard there is a direct option to import from Access and you will not even need to configure a DSN (but that is added after the article you refer was published),

Two more things you should know:

1) in the TRIAL you can import 2 tables per job and no more

2) As far as I remember the Northwind database has 'relationships'. To import those as Foreign Keys in SQLyog the security settings for teh access file must be modified. I will try to find the link about this for you

[peterlaursen]

Here is the link: http://www.webyog.com/en/whitepapers/Preparing_Access__Migration.pdf

[knowledgenotebk]

'peterlaursen' wrote:

To import from Access you will not need the 'myODBC' driver, but only the driver for Access/.mdb (that ships with Windows). SQLyog will not need a driver to connect to MySQL – but needs it in order to connect to Access. Actually in the wizard there is a direct option to import from Access and you will not even need to configure a DSN (but that is added after the article you refer was published),

Two more things you should know:

1) in the TRIAL you can import 2 tables per job and no more

2) As far as I remember the Northwind database has 'relationships'. To import those as Foreign Keys in SQLyog the security settings for teh access file must be modified. I will try to find the link about this for you

“Actually in the wizard there is a direct option to import from Access”,

Yeah, Peter, after posting the question, I did exactly that, and it worked. Now, I have two more questions.

A) MySQL may not be a solution for me… someone on mySQL ng just alerted me that I may not be able to bundle it in my software distribution…

😎 I'm not a MySQL guy, not sure why the following “mySQL favored” DDL statement generated by Webyog failed…

alter table `myDB`.`myTBL`

change `future` `future` bit(1) NULL ,

change `active` `active` bit(1) default '1' NOT NULL;

Thanks.

Don

[peterlaursen]

ALTER TABLE `test`.`bittest`

CHANGE `b` `b` BIT(1) DEFAULT '1' NOT NULL

.. return error: “Invalid default value for 'b'” Same error you get?

Values for BIT will need to specified in base-2/binary notation and not as a decimal number. So the correct statement is

ALTER TABLE `test`.`bittest`

CHANGE `b` `b` BIT(1) DEFAULT b'1' NOT NULL

.. btu that cannot be specified from the SQLyog GUI. If I try I get

alter table `test`.`bittest`

change `b` `b` bit(1) default 'b'1'' NOT NULL

.. what is equally wrong.

So we have a bug in SQLyog here: “Defaults for BIT type cannot be specified”. That was missed in version 8,8 where release notes read:

Quote:

Hexadecimal and binary/base2 strings can now be entered for any datatype using ” x’..’ ” and ” b’..’ ” notation in GRIDs. SQLyog will now not ‘quote’ such values starting with “x’” and “b’” when performing DML operations from the GRIDs.Implemented support for the BIT datatype. In DATA and RESULT tab BIT’s will now display using ” b’..’ ” notation what in combination with the point above ensures that BIT data are updateable.

A hint about the BIT type: In MySQL it is often more wise to use a TINYINT(1) as the mapping of an Access YES|NO type.

This is a famous Blog about the issues with BIT implementation in MySQL: http://www.xaprb.com/blog/2006/04/11/bit-values-in-mysql/ Most of the bugs listed here are fixed but the basic concerns about the implementation are still relevant.

In particular note that a BIT is not a BOOL/BOOLEAN. BOOLEAN in MySQL is (still) a synonym for TINYINT(1)

[peterlaursen]

I have added the bug to our issue tracker: http://code.google.com/p/sqlyog/issues/detail?id=1734

[Author]

Posts