• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1368
  • Last Modified:

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};Server=myserver.org;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
  • 3
  • 2
1 Solution
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
gordonwwaughAuthor 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
gordonwwaughAuthor 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.
gordonwwaughAuthor 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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now