ODBC Connection Import Tables to MS Access fails with "Cannot define field more than once"

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). Is there anywhere to look to find out more information about why these tables are failing? Here is a screenshot of the data in one of the failing tables:

And the schema:

I thought maybe it was the field named 'Year' since that's a function in Access, but there's another table without a year field that's failing. That table had too many fields for a screenshot but here is the list of fields in that table:

PlotID

State
StudySite
Watershed
Elevation
ElevationModifier
Aspect
Locale
Directions
OwnershipStatus
OwnerContactInfo
DendrobandInstall
PaintTrees
CoreTrees
BiophysicalRegion
ForestType
Comments
Origin
HavePermission

Any ideas on how to import these failing tables? I tried exporting the create table SQL but I couldn't figure out how to get the exported SQL to play nice in MS Access. I've spent 10+ hours on this and it's ridiculous! Unfortunately I can't change any of the existing field names.

Thanks!

Answer 1

just had the same issue. I finally figured out my problem is that I have 2 tables with the same table name in different schemas in mySQL. So I dropped the useless schema and redo the connection, and it worked.

For example: I want to connect a schema named share to access, and the schema share has a table named visits. And in my SQL, I have another schema named trouble, which also has a table named visits. When I try to connect schema share to access, the software cannot figure out which table named visits to be connected to (share.visits vs. trouble.visits).

Hope this helps. Good luck!