ODBC -call failed

[mdfOffman]

I connected my database to my remote mySQL server. The connection succeeded and I could see all my tables and link the ones I wanted. But, when I double clik on the tables, it shows all the field names correctly but all the field data is "Name?" with the error message "ODBC-call failed"

I don't understand how it could read the field names/columns correctly whenthe ODBC call failed. I also don't understand why the call fails when I can see all the tables in the navigator.

Can anyone help?
Thanks,
mdfOffman

 

[cheekybuddha]

Please issue the following command in MySQL and paste the results here:

SHOW CREATE TABLE YourTableName;

Does the table have a [visible] Primary Key?

Please also post your connection string for your linked table. You can get it by running the following command in the VBA Immediate Window (Ctrl+G):

?CurrentDb.TableDefs("YourTableName").Connect

(If it shows sensitive data like UID/PWD/server address then replace with x's)

 

[mdfOffman]

Thanks so much for your prompt response. I don't think I can do Show Create Table as my d/b is remote where I don't have terminal access. But I have full access to all my files in DBeaver - where I created the tables and set the primary keys. And I can even work with the tables in LibreOffice Base so my error is clearly in Access somewhere.
Refresh link for each table seems to work but i still get the ODBC Failed error.
So I put your connection string request in Immediate VBA and got the error "Runtime Error 3265 - Item not found in this collection."

I regret I'm lost

 

[GPGeorge]

As David suggested, the problem is probably that Access doesn't recognize the primary keys in your linked tables. Just because they are designated in the database doesn't guarantee Access can identify them correctly. This is most often seen when linking to a View with multiple joined tables. When creating linked tables, Access may not be able to identify the Primary Key. I haven't used MySQL, so there may be more issues. This, however, is the predominant problem.

Open the tables in design view in Access and look for the Primary Key designators. Are they there?

 

[mdfOffman]

Yes, all the tables show the right primary key in Design view though they are shown as Long Integer instead of the Integer setting in mySQL. I don't know if that matters

 

[mdfOffman]

I should disclose here that I am accessing Windows server on a remote desktop from my Mac whiich is linking to a different remote server with the data source. I guess there is some networking issue involved.

 

[cheekybuddha]

But I have full access to all my files in DBeaver - where I created the tables and set the primary keys.

OK, so open a new SQL script window in DBeaver and run the SHOW CREATE TABLE command for one of your tables.

You can change the Results from Grid to Text to enable you to copy the Create Table output for pasting here (use code tags).

(Or you can just copy the field result directly from the grid view)

Also post your connection string - it will reveal what ODBC options you have set.

 

[cheekybuddha]

they are shown as Long Integer instead of the Integer setting in mySQL. I don't know if that matters

It shouldn't matter if they are defined as INT in MySQL

I guess there is some networking issue involved.

It shouldn't matter if you are using Remote Desktop

 

[mdfOffman]

tbladdress "CREATE TABLE `tbladdress` (
`pkAddress` int(10) NOT NULL AUTO_INCREMENT,
`AddressType` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`NickName` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`Street1` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`Street2` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`City` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`State` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`AddressNotes` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`Zip` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`fkCountry` int(10) DEFAULT NULL,
PRIMARY KEY (`pkAddress`),
KEY `fkCountry` (`fkCountry`),
CONSTRAINT `tbladdress_ibfk_1` FOREIGN KEY (`fkCountry`) REFERENCES `tblcountries` (`pkCountry`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=282 DEFAULT CHARSET=utf8mb4"


DBeaver and LibreOffice Base connect with JDBC, not ODBC :-(

 

[cheekybuddha]

DBeaver and LibreOffice Base connect with JDBC, not ODBC :-(

Yes, we need to see the Access linked table connection string.

Run the second command I showed above from the Immediate Window of the VBA editor (Ctrl+G to open it).

One piece of advice - if your MySQL server is on a linux host, use all lower-case for naming your tables and fields. Linux is case-sensitive and it can bite you in the behind when you least expect it!

 

[mdfOffman]

I really appreciate all your help
So I put your connection string request in Immediate VBA and got the error "Runtime Error 3265 - Item not found in this collection."

My mySQL is on Windows!!

 

[cheekybuddha]

got the error "Runtime Error 3265 - Item not found in this collection."

You did remember to change the table name to a linked table name?

My mySQL is on Windows!!

OK, less of an issue then!

 

[GPGeorge]

tbladdress "CREATE TABLE `tbladdress` (
`pkAddress` int(10) NOT NULL AUTO_INCREMENT,
`AddressType` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`NickName` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`Street1` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`Street2` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`City` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`State` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`AddressNotes` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`Zip` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`fkCountry` int(10) DEFAULT NULL,
PRIMARY KEY (`pkAddress`),
KEY `fkCountry` (`fkCountry`),
CONSTRAINT `tbladdress_ibfk_1` FOREIGN KEY (`fkCountry`) REFERENCES `tblcountries` (`pkCountry`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=282 DEFAULT CHARSET=utf8mb4"


DBeaver and LibreOffice Base connect with JDBC, not ODBC :-(

For information: We had a presentation on using DBeaver a couple of months ago. Kevin mentioned the JDBC connection.

 

[mdfOffman]

Duh!!!!!!!!!
ODBC;DRIVER={MySQL ODBC 9.0 ANSI Driver};UID=myUID;USER=myUsrName;SERVER=62.3.51.120;PORT=3306;PASSWORD={myPassword};NO_SCHEMA=1;DFLT_BIGINT_BIND_STR=1;DB=gts;DATABASE=gts;FILEDSN=C:\Users\Administrator\Documents\62.3.51.120.dsn;

My RDP user name is "trader", not Administrator. Could that be a problem and where do i change it?

 

[cheekybuddha]

First, please edit your post to hide the IP of your server - no point advertising this to the world for no reason!

DRIVER={MySQL ODBC 9.0 ANSI Driver}

Try using the Unicode driver. Since the charset of your tables seems to be UTF8 or UTF8MB4, you probably want unicode.

Are you able to open the DSN file in a text editor and paste the contents here? (again scrubbing any sensitive info).

I'm curious if you have any other options set. I can see you have DFLT_BIGINT_BIND_STR and NO_SCHEMA set - normally you pass these via a numeric value, but I guess this way works as well.

 

[cheekybuddha]

My RDP user name is "trader", not Administrator. Could that be a problem and where do i change it?

I think this is a red herring - RDP shouldn't have a bearing on what your Access on the server can do.

 

[mdfOffman]

Yes, I tried Unicode but same error. And I discovered i was not telling it properly where to save the dsn so i did that and relinked. Unfortunately the same error.

I am in Hong Kong so I have to go to bed now

 

[mdfOffman]

Thank you again for all your help

 

[cheekybuddha]

Sleep well!

Thoughts for tomorrow:

You are using MyODBC connector version 9 - what version is your MySQL server?

If it is 8.x then try using the MyODBC 8 drivers instead.

 

[mdfOffman]

Whoa!! My version of mySQL is 5.7.10!!!! I have sent an urgent alert to Hostek .....