Syntax Error During Access Transfer

General usage of Access to MySQL

Moderator: jr

Post Reply
helen_tarnation
Posts: 1
Joined: Wed Dec 05, 2007 3:50 am

Syntax Error During Access Transfer

Post by helen_tarnation »

Hello,

I am new here so thank you for your help ahead of time. Before transferring my Access DB to MySQL with Access to MySQL, I created a MySQL DB with the same name as the Access DB. Then I walked through the transfer wizard. But none of the tables transferred properly because there is a syntax error.

Here is the error code:

[code]SETTINGS
--------
Moving data directly to MySQL server

TABLES
------------
- Creating 'ADDRESSES' failed
- SQL: CREATE TABLE `ADDRESSES` ( `ADDRESS_ID` INTEGER NOT NULL AUTO_INCREMENT, `STREET_NUMBER` VARCHAR(10), `BUILDING_NUMBER` VARCHAR(10), `CITY_TOWN` VARCHAR(30), `COUNTY_STATE_PROVINCE` VARCHAR(20), `AREA_LOCALITY` VARCHAR(20), `COUNTRY` VARCHAR(6), `ZIP_POSTCODE` VARCHAR(10) NOT NULL, PRIMARY KEY (`ADDRESS_ID`), INDEX (`ZIP_POSTCODE`)) TYPE=BDB
- Error: -2147217900 (80040E14) [MySQL][ODBC 3.51 Driver][mysqld-6.0.3-alpha-community]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 'TYPE=BDB' at line 1
- 0 records moved
- Creating 'AGE_CATEGORIES' failed
- SQL: CREATE TABLE `AGE_CATEGORIES` ( `AGE_CATEGORY_CODE` VARCHAR(4) NOT NULL, `AGE_CATEGORY_DESCRIPTION` VARCHAR(10), INDEX (`AGE_CATEGORY_DESCRIPTION`), PRIMARY KEY (`AGE_CATEGORY_CODE`)) TYPE=BDB
- Error: -2147217900 (80040E14) [MySQL][ODBC 3.51 Driver][mysqld-6.0.3-alpha-community]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 'TYPE=BDB' at line 1
- 0 records moved
- Creating 'FEATURES' failed
- SQL: CREATE TABLE `FEATURES` ( `FEATURE_ID` VARCHAR(4) NOT NULL, `FEATURE_NAME` INTEGER, `FEATURE_DESCRIPTION` VARCHAR(255), INDEX (`FEATURE_NAME`), PRIMARY KEY (`FEATURE_ID`)) TYPE=BDB
- Error: -2147217900 (80040E14) [MySQL][ODBC 3.51 Driver][mysqld-6.0.3-alpha-community]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 'TYPE=BDB' at line 1
- 0 records moved
- Creating 'PROPERTIES' failed
- SQL: CREATE TABLE `PROPERTIES` ( `PROPERTY_ID` INTEGER NOT NULL, `PROPERTY_ADDRESS_ID` INTEGER, `OWNER_USER_ID` INTEGER, `PROPERTY_TYPE_CODE` INTEGER, `DATE_ON_MARKET` VARCHAR(50), `DATE_OFF_MARKET` VARCHAR(50), `PROPERTY_NAME` VARCHAR(30), `PROPERTY_DESCRIPTION` TEXT, `GARAGE_YN` TINYINT DEFAULT 0, `PARKING_YN` TINYINT DEFAULT 0, `ROOM_COUNT` VARCHAR(2), `VENDOR_REQUESTED_PRICE` INTEGER DEFAULT 0, `PRICE_MIN` DECIMAL(19,4) DEFAULT 0, `PRICE_MAX` DECIMAL(19,4) DEFAULT 0, `OTHER_PROPERTY_DETAILS` TEXT, `USER_ID` VARCHAR(4), `SCHOOL_DIST` VARCHAR(20), `NEAREST_SUB` VARCHAR(10), `NEAREST_SUB_NAME` VARCHAR(5), INDEX (`PROPERTY_ADDRESS_ID`), INDEX (`DATE_OFF_MARKET`), INDEX (`DATE_ON_MARKET`), INDEX (`GARAGE_YN`), INDEX (`OTHER_PROPERTY_DETAILS`(100)), INDEX (`OWNER_USER_ID`), INDEX (`PARKING_YN`), INDEX (`PRICE_MAX`), INDEX (`PRICE_MIN`), PRIMARY KEY (`PROPERTY_ID`), INDEX (`PROPERTY_ADDRESS_ID`), INDEX (`PROPERTY_DESCRIPTION`(100)), INDEX (`PROPERTY_NAME`), INDEX (`PROPERTY_TYPE_CODE`), INDEX (`ROOM_COUNT`), INDEX (`USER_ID`), INDEX (`VENDOR_REQUESTED_PRICE`)) TYPE=BDB
- Error: -2147217900 (80040E14) [MySQL][ODBC 3.51 Driver][mysqld-6.0.3-alpha-community]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 'TYPE=BDB' at line 1
- 0 records moved
- Creating 'PROPERTY_FEATURES' failed
- SQL: CREATE TABLE `PROPERTY_FEATURES` ( `PROPERTY_ID` VARCHAR(4) NOT NULL, `FEATURE_ID` INTEGER, `FEATURE_VALUE` DECIMAL(19,4), `PROPERTY_FEATURE_DESCRIPTION` VARCHAR(255), INDEX (`FEATURE_ID`), PRIMARY KEY (`PROPERTY_ID`)) TYPE=BDB
- Error: -2147217900 (80040E14) [MySQL][ODBC 3.51 Driver][mysqld-6.0.3-alpha-community]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 'TYPE=BDB' at line 1
- 0 records moved
- Creating 'PROPERTY_PHOTOS' failed
- SQL: CREATE TABLE `PROPERTY_PHOTOS` ( `PHOTO_SEQ` VARCHAR(255) NOT NULL, `PROPERTY_ID` INTEGER, `PHOTO_TITLE` VARCHAR(50), `PHOTO_DESCRIPTION` TEXT, `PHOTO_FILENAME` VARCHAR(50), INDEX (`PHOTO_FILENAME`), INDEX (`PHOTO_TITLE`), PRIMARY KEY (`PHOTO_SEQ`), INDEX (`PROPERTY_ID`)) TYPE=BDB
- Error: -2147217900 (80040E14) [MySQL][ODBC 3.51 Driver][mysqld-6.0.3-alpha-community]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 'TYPE=BDB' at line 1
- 0 records moved
- Creating 'PROPERTY_TYPES' failed
- SQL: CREATE TABLE `PROPERTY_TYPES` ( `PROPERTY_TYPE_CODE` VARCHAR(4) NOT NULL, `PROPERTY_TYPE_DESCRIPTION` INTEGER, `PROPERTY_ID` INTEGER, PRIMARY KEY (`PROPERTY_TYPE_CODE`), INDEX (`PROPERTY_ID`), INDEX (`PROPERTY_TYPE_DESCRIPTION`)) TYPE=BDB
- Error: -2147217900 (80040E14) [MySQL][ODBC 3.51 Driver][mysqld-6.0.3-alpha-community]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 'TYPE=BDB' at line 1
- 0 records moved
- Creating 'ROOM_TYPES' failed
- SQL: CREATE TABLE `ROOM_TYPES` ( `ROOM_TYPE_CODE` INTEGER NOT NULL AUTO_INCREMENT, `ROOM_TYPE_DESCRIPTION` VARCHAR(255), PRIMARY KEY (`ROOM_TYPE_CODE`)) TYPE=BDB
- Error: -2147217900 (80040E14) [MySQL][ODBC 3.51 Driver][mysqld-6.0.3-alpha-community]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 'TYPE=BDB' at line 1
- 0 records moved
- Creating 'ROOMS' failed
- SQL: CREATE TABLE `ROOMS` ( `PROPERTY_ID` VARCHAR(4) NOT NULL, `ROOM_NUMBER` VARCHAR(50), `ROOM_TYPE_CODE` INTEGER, `ROOM_SIZE` VARCHAR(255), `OTHER_ROOM_DETAILS` DATETIME, PRIMARY KEY (`PROPERTY_ID`), INDEX (`ROOM_TYPE_CODE`)) TYPE=BDB
- Error: -2147217900 (80040E14) [MySQL][ODBC 3.51 Driver][mysqld-6.0.3-alpha-community]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 'TYPE=BDB' at line 1
- 0 records moved
- Creating 'USER_CATEGORIES' failed
- SQL: CREATE TABLE `USER_CATEGORIES` ( `USER_CATEGORY_CODE` VARCHAR(4) NOT NULL, `USER_CATEGORY_DESCRIPTION` VARCHAR(50), INDEX (`USER_CATEGORY_DESCRIPTION`), PRIMARY KEY (`USER_CATEGORY_CODE`)) TYPE=BDB
- Error: -2147217900 (80040E14) [MySQL][ODBC 3.51 Driver][mysqld-6.0.3-alpha-community]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 'TYPE=BDB' at line 1
- 0 records moved
- Creating 'USER_PROPERTY_HISTORY' failed
- SQL: CREATE TABLE `USER_PROPERTY_HISTORY` ( `USER_ID` VARCHAR(4) NOT NULL, `PROPERTY_ID` INTEGER, `DATESTAMP` DATETIME NOT NULL, PRIMARY KEY (`DATESTAMP`), INDEX (`PROPERTY_ID`)) TYPE=BDB
- Error: -2147217900 (80040E14) [MySQL][ODBC 3.51 Driver][mysqld-6.0.3-alpha-community]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 'TYPE=BDB' at line 1
- 0 records moved
- Creating 'USER_SEARCHES' failed
- SQL: CREATE TABLE `USER_SEARCHES` ( `USER_ID` VARCHAR(4) NOT NULL, `SEARCH_SEQ` INTEGER, `SEARCH_DATE_TIME` DATETIME, `SEARCH_STRING` VARCHAR(255), PRIMARY KEY (`USER_ID`), INDEX (`SEARCH_DATE_TIME`)) TYPE=BDB
- Error: -2147217900 (80040E14) [MySQL][ODBC 3.51 Driver][mysqld-6.0.3-alpha-community]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 'TYPE=BDB' at line 1
- 0 records moved
- Creating 'USERS' failed
- SQL: CREATE TABLE `USERS` ( `USER_ID` VARCHAR(4) NOT NULL, `AGE_CATEGORY_CODE` INTEGER, `USER_CATEGORY_CODE` VARCHAR(50), `USER_ADDRESS_ID` INTEGER, `BUYER_YN` TINYINT DEFAULT 0, `SELLER_YN` TINYINT DEFAULT 0, `FIRST_NAME` VARCHAR(50) NOT NULL, `DATE_REGISTRATION` DATETIME, `PASSWORD` VARCHAR(20), `MIDDLE_NAME` VARCHAR(50), `LAST_NAME` VARCHAR(50) NOT NULL, `OTHER_USER_DETAILS` TEXT, `ADDRESS_ID` INTEGER NOT NULL, `PHONE` VARCHAR(50) NOT NULL, `EMAIL` VARCHAR(30) NOT NULL, INDEX (`ADDRESS_ID`), INDEX (`ADDRESS_ID`), INDEX (`AGE_CATEGORY_CODE`), INDEX (`BUYER_YN`), INDEX (`LAST_NAME`), INDEX (`PASSWORD`), PRIMARY KEY (`USER_ID`), INDEX (`SELLER_YN`), INDEX (`USER_ADDRESS_ID`), INDEX (`USER_CATEGORY_CODE`), INDEX (`USER_CATEGORY_CODE`)) TYPE=BDB
- Error: -2147217900 (80040E14) [MySQL][ODBC 3.51 Driver][mysqld-6.0.3-alpha-community]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 'TYPE=BDB' at line 1
- 0 records moved
[/code]

Perhaps MySQL is not configured properly? Or should I not transfer the Access DB into a preexisting MySQL database? Am I selecting the incorrect storage engine? I tried every type of storage engine though, and I got the same result.
Thanks for your time,
regards,
HT.
Post Reply