Syntax Error in Create Table Statement in MS Access but not in phpmyadmin

Question

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;

Access does not use apostrophe or quote to define object names. It uses `[ ]` and those are only needed if name contains space or special character or is a reserved word (such as `[Year]`). — June7, Apr 07 '22 at 18:34
Also, it appears COMMENT keyword is not recognized by Access. In my test, CREATE TABLE works when I remove COMMENT clause. — June7, Apr 07 '22 at 18:40
Review https://learn.microsoft.com/en-us/office/vba/access/concepts/structured-query-language/define-relationships-between-tables-using-access-sql — June7, Apr 07 '22 at 18:56
Thank you. I updated the query to remove all apostrophes, use brackets around [year], removed all comments and removed the relationship and key definitions because I don't really need them at the moment. But still getting syntax error. I updated the code in my post to reflect my most recent attempt — xanabobana, Apr 07 '22 at 19:05
There's not such thing as `InnoDB` or "Engine" in access. There is no concept for "CHARSET". This is not how you describe a PRIMARY KEY in access database. There is no `varchar` type in Access. tHave you read up at all on Access DB CREATE TABLE syntax? You can't just take DDL from one RDBMS and apply it to another RDBMS. — JNevill, Apr 07 '22 at 19:08
Regarding DECIMAL review https://stackoverflow.com/questions/180929/how-do-i-create-a-decimal-field-in-access-with-alter-table. Actually, VARCHAR does work to create short text field. `DEFAULT NULL` fails. Do you want a compound key? `Year` without [ ] does work but I would still recommend not naming field with function name. — June7, Apr 07 '22 at 19:20
I was testing with `CurrentDb.Execute`. Apparently `CurrentProject.Connection.Execute` will handle `DECIMAL` and `DEFAULT`. Learned something new. — June7, Apr 07 '22 at 20:16

HansUp · Accepted Answer · 2022-04-07T21:21:52.113

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:

int field type has a fixed length. Access will complain if you try to do int(<some number>)
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.
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.
Access does not accept your original PRIMARY KEY constraint. I re-wrote it following the pattern: CONSTRAINT <constraint name> PRIMARY KEY (<field list>)

Thank you! I tried linking the tables but I get the same 'Cannot define field more than once' error. I was able to create the table using your code, but now I'm not sure how to effectively copy over 32,000 records. I tried creating insert into statements, and just copy/pasting but Access says 'The text is too long to be edited'. Any ideas on that? — xanabobana, Apr 08 '22 at 13:02
I don't know why your insert attempt fails. As an alternative, can you export the MySQL table's data to CSV and then get at that CSV from Access to import it there? — HansUp, Apr 08 '22 at 13:22

Syntax Error in Create Table Statement in MS Access but not in phpmyadmin

1 Answers1