I need to create an Access database from an existing mySQL database. I was able to import some of the tables using the ODBC connection but a few of the tables were erroring out (saying 'Cannot define field more than once'). I couldn't figure out why I was getting that error (There are no fields with duplicate names in any of the failing tables, nor any fields with special characters), so I decided to just generate the SQL from Toad for those tables.
The SQL I get from Toad for the first table is the following, which gives no error in phpmyadmin or toad. Why is Access telling me there is a syntax error? Access also didn't highlight or underline anything to give me an indication of where the syntax error might be.
I tried replacing the ` quote with a single quote ' and also double quotes "
I also tried removing the ENGINE=InnoDB DEFAULT CHARSET=utf8 piece.
Any idea what might be causing the issue in Access? Or another way to get these failing tables into Access?
Thank you!
CREATE TABLE tblHemisphericalPhoto (
PlotID varchar(10) NOT NULL,
SubplotID int(11) NOT NULL,
[year] int(11) NOT NULL,
fldStorageLocation varchar(100) NOT NULL,
fldFilename varchar(100) NOT NULL,
fldPhotoLocation varchar(45) NOT NULL,
fldLensHeight decimal(6,2) DEFAULT NULL,
PRIMARY KEY (fldStorageLocation,fldFilename,[year],PlotID,SubplotID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;