ODBC -call failed

M

mdfOffman

New member
Local time
Today, 16:53
Joined
Oct 13, 2024
Messages
12
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
 
Please issue the following command in MySQL and paste the results here:
Code: 
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):
Code: 
?CurrentDb.TableDefs("YourTableName").Connect
(If it shows sensitive data like UID/PWD/server address then replace with x's)
 
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
 
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?
 
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
 
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.
 
mdfOffman said:
But I have full access to all my files in DBeaver - where I created the tables and set the primary keys.
Click to expand...
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.
 
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 :-(
 
mdfOffman said:
DBeaver and LibreOffice Base connect with JDBC, not ODBC :-(
Click to expand...
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!
 
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!!
 
mdfOffman said:
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 :-(
Click to expand...
For information: We had a presentation on using DBeaver a couple of months ago. Kevin mentioned the JDBC connection.

 
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?
 
First, please edit your post to hide the IP of your server - no point advertising this to the world for no reason!

mdfOffman said:
DRIVER={MySQL ODBC 9.0 ANSI Driver}
Click to expand...
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.
 
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 :-)
 
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.
 
Whoa!! My version of mySQL is 5.7.10!!!! I have sent an urgent alert to Hostek .....
 
You must log in or register to reply here.

Similar threads

N
Solved Insert data in phpmyadmin MYSL from MS Access
Replies
4
Views
151
nector
N
P
Refresh Links
Replies
15
Views
586
prasadgov
P
F
Solved SQL statement works in Access but not via ODBC
Replies
15
Views
223
Pat Hartman
P
monheimx9
Pass-Through Query - Connecting to another Access Backend
Replies
9
Views
299
The_Doc_Man
The_Doc_Man
N
ODBC 17 for SQL server not able to connect
Replies
18
Views
1,450
nector
N

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom