MySQL 3146: ODBC--call failed

Using VBA code, I have tried to connect a MySQL database to a MS Access frontend. However, I get the Access error message "MySQL ODBC 5.1 Driver." I am using the following connection string:

ODBC;Driver={MySQL ODBC 5.1 Driver};;Database=G_ib001;UID=myuserid;PWD=mypassword;

However, the connection is successful when I use the MySQLODBC Connector control panel and click its Test button.

The tdf.Append code below triggers the error message. I have checked to ensure that the table does exist in the backend MySQL database.

    Do Until rst.EOF
        strTable = rst!TableName
        ' Create a new TableDef object
        Set tdf = db.CreateTableDef(strTable)
        ' Set the Connect property to establish the link
        tdf.Connect = strConnect
        tdf.SourceTableName = strTable
        Debug.Print "About to append table: " & strTable
        ' Append to the database's TableDefs collection
        db.TableDefs.Append tdf
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I think you're missing the dbAttachedTable attribute property:

Try this:
dim oTBL as tabledef
dim oDB as database

set oDB = currentdb
set oTBL = oDB.CreatetableDef(strTable, dbAttachedTable, strTable, strConnect)
oDB.TableDefs.Append oTBL

or in your code:

tdf.attributes = dbAttachedTable
tdf.Connect = strConnect
tdf.SourceTableName = strTable
gordonwwaughConsultantAuthor Commented:
When I do this, I get an error on the "tdf.attributes = dbAttachedTable" statement.

Run-time error 3001: Invalid argument.
Yes, I checked, you cannot set the dbAttachedTable as attribute.
An other option is to work with a DSN file.
    With tdf
        .Connect = "ODBC;DSN=[DSN_NAME];"
        .Connect = ConnectStr
        .SourceTableName = "strTable"        
    End With
db.TableDefs.Append tdf
gordonwwaughConsultantAuthor Commented:
I had already tried that--with the same result.

The vast majority of my program is straight out of the Access Cookbook. In their program, they use SqlServer rather than MySQL. Their program does not explicitly tell Access that this is a linked table. So, I am guessing that Access is smart enough to know this is an attached table because of the ODBC in the connection string.
gordonwwaughConsultantAuthor Commented:
I found the problem!

The list of table names that I used to connect to the MySQL backend are in upper-lower case. However, the corresponding table names in MySQL are in all lowercase. When I changed the list of table names to all lowercase (to be consistent with MySQL), everything worked!

I knew that MySQL respects case in table names whereas Access ignores case. However, I just didn't think about it until now.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.