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;

CodePudding user response：

Access and MySQL SQL dialects don't match well. Furthermore Access DDL comes with details which are unwelcome surprises to many people (see especially Note 2 below). So translating a MySQL CREATE TABLE statement to Access SQL can be challenging.

It would be easier to just import the MySQL table into Access. But, since you're getting an error attempting the import, I would try to link the table instead and then run a "make table" query to pull the data into a new Access table:

SELECT * INTO NewAccessTableName FROM LinkedTableName

Once you have the Access table, you can add your primary key and modify field properties as needed in the table's Design View.

However, if you must or want to use Access DDL, here is a version successfully tested in Access:

strCreate = "CREATE TABLE tblHemisphericalPhoto (PlotID varchar(10) NOT NULL, " & _
    "SubplotID int NOT NULL, [year] int NOT NULL, fldStorageLocation varchar(100) " & _
    "NOT NULL, fldFilename varchar(100) NOT NULL, fldPhotoLocation varchar(45) " & _
    "NOT NULL, fldLensHeight decimal(6,2) DEFAULT Null, " & _
    "CONSTRAINT pkey PRIMARY KEY (fldStorageLocation,fldFilename,[year],PlotID,SubplotID))"

CurrentProject.Connection.Execute strCreate

Notes:

1. int field type has a fixed length. Access will complain if you try to do int(<some number>)
2. decimal field type can be a "gotcha" issue in Access. It is legal in a CREATE TABLE, but only when the statement is executed via OleDb. It works from CurrentProject.Connection.Execute because that is an ADO method and therefore uses OleDb.
3. DEFAULT is also legal in Access DDL, but must be executed via OleDb. However I don't see why it's useful here. When you add a row without supplying a value for that field, it will be Null unless you specify some other default.
4. Access does not accept your original PRIMARY KEY constraint. I re-wrote it following the pattern: CONSTRAINT <constraint name> PRIMARY KEY (<field list>)