| Bug #7987 | ODBC System DSN does not work - see the lengthy thread on the Forum | ||
|---|---|---|---|
| Submitted: | 18 Jan 2005 13:20 | Modified: | 26 Jul 2007 0:37 |
| Reporter: | Martin Birk | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S3 (Non-critical) |
| Version: | 3.51.10.00 | OS: | Windows (Windows) |
| Assigned to: | CPU Architecture: | Any | |
[18 Jan 2005 17:35]
MySQL Verification Team
Thank you for the bug report.
[26 Jan 2005 17:27]
Yossi Ne'eman
I have a similar problem here, and the MS ODBC trace shows that on the first time I try to open MS Access, that it is trying to log into the MySQL server as 'Admin'@FQDN... this is happening at 2 offices which never previously used MyODBC. We are using the same version as above. Now, if we go thru the Linked Tables Manager in Access, we can refresh the links and access the data, but that only works until Access itself is closed fully.
[6 Feb 2005 4:05]
Vegard M
I having the same problems with MyODBC 3.5.10. I just installed MySQL Server 4.1 without any problems connecting to the server with MySQL Administrator and MySQL Query Browser. I created a database and some tables without problems. When creating a DSN and testing it I got the message, "Success; connection was made!" In Visual Studio .NET 2003 I went to test my DSN. Choosing "Tools - Connect to Database..." brings up the "Data Link Properties" window. I Choose "Microsoft OLE DB provider for ODBC Drivers" in the Providers tab and my newly created DSN in Connection tab, and then tried "Test Connection". The error I got was "Microsoft Data Link Error: Test connection failed because of an error in initializing provider. Catastrophic failure" I tried uninstalling and reinstalling MyODBC 3.5.10 three or four times with the same result.
[15 Jun 2005 0:17]
John Collins
Hi all In MySQL, create a user "Admin" with your particular host name and permissions. I don't know about setting a password as I have not tried, and the database isn't sensitive enough to require a password anyway. Access looks for the user "Admin" when trying to connect to a MySQL database, despite what you may put in the MyODBC settings. Once a connection has been established, I'm not sure if Access uses those settings, or uses the Admin login exclusively. This should now allow you access the tables without having to relink them on startup.
[12 Jan 2006 22:30]
Mark Boyden
I have a similar issue: I'm connecting to an internet-based MySQL server from a Windows computer using Microsoft Access 2003. I've loaded/used the ODBC Drivers version 3.51.10.0 and 3.51.12.0 and 5.0.0-alpha. Each gives a similar message, but a little different result as outlined below. Mind you I can also get the connection success when testing the connection in the ODBC Data Sources. It doesn't appear to be relevant to the version of MySQL I'm using because I was able to connect to the same database on a different server using the same version of MySQL. This setup worked on the old host using: Access 2003, PHP v4.2.1, MySQL v3.23.49, MySQL ODBC Drivers v3.51.10.0, SunOS infinity 5.8 Generic_108528-13. The setup didn't work on the new host using: Access 2003, PHP v4.4.0, MySQL v3.23.49, MySQL ODBC Driver v3.51.10.0 or 3.51.12.0 or 5.0.0-alpha, Linux 2.4.20-021stab022.8.777-enterprise. This is also a cPanel-based machine. Microsoft Access 2003 error messages: Results with 3.51.10.0: ODBC--call failed. [MySQL][ODBC 3.51 Driver]Access denied for user: 'dbusername@localipaddress'to database 'dbname' (#1044) Results with 3.51.12.0: ODBC--call failed. [MySQL][ODBC 3.51 Driver]User cancelled. (#0) Results with 5.0.0-alpha: Reserved error (-7701); there is no message for this error. Finally, I then tried it with OpenOffice Base v2.0. 5.0.0-alpha didn't work. 3.5.12.0 Did (but doesn't keep a connection and I have to re-open the database if I don't use it for a little time). So, I'm not exactly sure what the issue is, but it appears that Access doesn't like something in terms of the interactions and responses with the ODBC connector. The test in the ODBC data sources setup always tested fine, but the real interaction with the database was the issue. I'd be happy to answer additional questions. Mark
[16 Jun 2006 11:09]
Murphy Wong
Dear all, Please try install ODBC driver selecting "Typical" rather than "Complete". Also, try installing the old version 3.51.06 and do a upgrade with this latest version. This solved my MSAccess connection problem. Regards, Murphy
[20 Jun 2006 15:56]
Linker Mills
Uninstalling and then re-installing under "typical" works for me as well. Thanks for the the solution to my issue. Linker -- Baltimore, MD
[27 Jul 2006 23:11]
christian bahnsen
You guys rule!!!! Uninstalling the newer ODBC connector then installing 3.51.06, and adding an "admin" user to the mysql database allowed my copy of Access 2002 to connect to the DSN. FYI: I didn't see the "Typical" option during install, but it worked anyway. Thanks a million.
[9 Aug 2006 0:00]
Sourav Choudhury
Nope, not working for me. I'm using Access 2000, and ODBC 3.51.12 I have added a new user "admin", I have installed the typical one, (not complete) and I couldn't find the 3.51.06 release ... Same problem, while testing, it tells connection was successfully made, but while connecting in reality to export my Access database to mysql, gives me the message User cancelled (#0) :(:(:(
[26 Jul 2007 0:37]
Jim Winstead
I've tested both user and system DSNs with Microsoft Access 2003 and Connector/ODBC 3.51.18 (not yet released), and they do not exhibit any of these problems.
[12 Sep 2007 2:37]
Benjamin Savittiere
Help! I am using Access 2003. The OBDC window will not open at all. I get to the point of "Get external Data, then select "OBDC Databases()", and nothing happens. I have created the DSN and it works from Excel, but not Access. I have 2 other computers where it does work, and 2 that it does not. What is the problem? Ben Savittiere
[1 Oct 2007 13:58]
Susanne Ebrecht
Hi Bejamin, we can't help you, without more details. Are you pretty sure that the behaviour occurs because of DSN? Because this bug here is because of DSN problems. If your problems are in another way, please open a new bug. Which system do you use? Can you add a trace file? Regards, Susanne
Description: I install the 3.51.10.00 driver (with the file MyODBC-3.51.10-2-win.exe) Create a System DSN, press Test, connection OK. (I have even installed mySQL Administrator, works fine). 1) Then create a new MS Access database (OfficeXP) Create a linked table, select the system DSN just created--> Error: "ODBC--call failed. [MySQL][ODBC 3.51 Driver]Could not determine the driver name so could not lookup setup library.(#0)" 2) Then I create a USER DSN. This works the first time when creating a linked table. When closing and then reopening Access, you get the following error: "ODBC--connection to 'xxx' failed. I could not make the driver output a trace file (myodbc.log). Instead I have submitted the ODBC trace log from Windows for the 2 situations: 1)Using a System DSN Windows ODBC-trace when trying to use the System DSN: Licensstyring 12e4-17dc ENTER SQLAllocConnect HENV 07F11540 HDBC * 0013C694 Licensstyring 12e4-17dc EXIT SQLAllocConnect with return code 0 (SQL_SUCCESS) HENV 07F11540 HDBC * 0x0013C694 ( 0x07f115e8) Licensstyring 12e4-17dc ENTER SQLSetConnectOption HDBC 07F115E8 SQLINTEGER 103 <SQL_LOGIN_TIMEOUT> SQLPOINTER 0x00000014 Licensstyring 12e4-17dc EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS) HDBC 07F115E8 SQLINTEGER 103 <SQL_LOGIN_TIMEOUT> SQLPOINTER 0x00000014 (BADMEM) Licensstyring 12e4-17dc ENTER SQLSetConnectAttrW SQLHDBC 07F115E8 SQLINTEGER 30002 <unknown> SQLPOINTER [Unknown attribute 30002] SQLINTEGER -3 Licensstyring 12e4-17dc EXIT SQLSetConnectAttrW with return code 0 (SQL_SUCCESS) SQLHDBC 07F115E8 SQLINTEGER 30002 <unknown> SQLPOINTER [Unknown attribute 30002] SQLINTEGER -3 Licensstyring 12e4-17dc ENTER SQLDriverConnectW HDBC 07F115E8 HWND 01010610 WCHAR * 0x74329A38 [ -3] "******\ 0" SWORD -3 WCHAR * 0x74329A38 SWORD 2 SWORD * 0x00000000 UWORD 1 <SQL_DRIVER_COMPLETE> Licensstyring 12e4-17dc EXIT SQLDriverConnectW with return code -1 (SQL_ERROR) HDBC 07F115E8 HWND 01010610 WCHAR * 0x74329A38 [ -3] "******\ 0" SWORD -3 WCHAR * 0x74329A38 SWORD 2 SWORD * 0x00000000 UWORD 1 <SQL_DRIVER_COMPLETE> DIAG [HY000] [MySQL][ODBC 3.51 Driver]Could not determine the driver name so could not lookup setup library. (0) Licensstyring 12e4-17dc ENTER SQLErrorW HENV 07F11540 HDBC 07F115E8 HSTMT 00000000 WCHAR * 0x0013C550 (NYI) SDWORD * 0x0013C59C WCHAR * 0x02A7DBD0 SWORD 4095 SWORD * 0x0013C588 Licensstyring 12e4-17dc EXIT SQLErrorW with return code 0 (SQL_SUCCESS) HENV 07F11540 HDBC 07F115E8 HSTMT 00000000 WCHAR * 0x0013C550 (NYI) SDWORD * 0x0013C59C (0) WCHAR * 0x02A7DBD0 [ 95] "[MySQL][ODBC 3.51 Driver]Could not determine the driver name so could not lookup setup library." SWORD 4095 SWORD * 0x0013C588 (95) Licensstyring 12e4-17dc ENTER SQLErrorW HENV 07F11540 HDBC 07F115E8 HSTMT 00000000 WCHAR * 0x0013C550 (NYI) SDWORD * 0x0013C59C WCHAR * 0x02A7DCA0 SWORD 3991 SWORD * 0x0013C588 Licensstyring 12e4-17dc EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND) HENV 07F11540 HDBC 07F115E8 HSTMT 00000000 WCHAR * 0x0013C550 (NYI) SDWORD * 0x0013C59C WCHAR * 0x02A7DCA0 SWORD 3991 SWORD * 0x0013C588 Licensstyring 12e4-17dc ENTER SQLFreeConnect HDBC 07F115E8 Licensstyring 12e4-17dc EXIT SQLFreeConnect with return code 0 (SQL_SUCCESS) HDBC 07F115E8 2) Using a User DSN Licensstyring 1748-1758 ENTER SQLAllocEnv HENV * 0013C3AC Licensstyring 1748-1758 EXIT SQLAllocEnv with return code 0 (SQL_SUCCESS) HENV * 0x0013C3AC ( 0x07f11540) Licensstyring 1748-1758 ENTER SQLAllocConnect HENV 07F11540 HDBC * 0013C3B8 Licensstyring 1748-1758 EXIT SQLAllocConnect with return code 0 (SQL_SUCCESS) HENV 07F11540 HDBC * 0x0013C3B8 ( 0x07f115e8) Licensstyring 1748-1758 ENTER SQLSetConnectOption HDBC 07F115E8 SQLINTEGER 103 <SQL_LOGIN_TIMEOUT> SQLPOINTER 0x00000014 Licensstyring 1748-1758 EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS) HDBC 07F115E8 SQLINTEGER 103 <SQL_LOGIN_TIMEOUT> SQLPOINTER 0x00000014 (BADMEM) Licensstyring 1748-1758 ENTER SQLSetConnectAttrW SQLHDBC 07F115E8 SQLINTEGER 30002 <unknown> SQLPOINTER [Unknown attribute 30002] SQLINTEGER -3 Licensstyring 1748-1758 EXIT SQLSetConnectAttrW with return code 0 (SQL_SUCCESS) SQLHDBC 07F115E8 SQLINTEGER 30002 <unknown> SQLPOINTER [Unknown attribute 30002] SQLINTEGER -3 Licensstyring 1748-1758 ENTER SQLDriverConnectW HDBC 07F115E8 HWND 00750654 WCHAR * 0x74329A38 [ -3] "******\ 0" SWORD -3 WCHAR * 0x74329A38 SWORD 2 SWORD * 0x00000000 UWORD 0 <SQL_DRIVER_NOPROMPT> Licensstyring 1748-1758 EXIT SQLDriverConnectW with return code -1 (SQL_ERROR) HDBC 07F115E8 HWND 00750654 WCHAR * 0x74329A38 [ -3] "******\ 0" SWORD -3 WCHAR * 0x74329A38 SWORD 2 SWORD * 0x00000000 UWORD 0 <SQL_DRIVER_NOPROMPT> DIAG [S1T00] [MySQL][ODBC 3.51 Driver]Access denied for user: '@%' to database 'itory2' (1044) Licensstyring 1748-1758 ENTER SQLErrorW HENV 07F11540 HDBC 07F115E8 HSTMT 00000000 WCHAR * 0x0013C274 (NYI) SDWORD * 0x0013C2C0 WCHAR * 0x02A7DBD0 SWORD 4095 SWORD * 0x0013C2AC Licensstyring 1748-1758 EXIT SQLErrorW with return code 0 (SQL_SUCCESS) HENV 07F11540 HDBC 07F115E8 HSTMT 00000000 WCHAR * 0x0013C274 (NYI) SDWORD * 0x0013C2C0 (1044) WCHAR * 0x02A7DBD0 [ 74] "[MySQL][ODBC 3.51 Driver]Access denied for user: '@%' to database 'itory2'" SWORD 4095 SWORD * 0x0013C2AC (74) Licensstyring 1748-1758 ENTER SQLErrorW HENV 07F11540 HDBC 07F115E8 HSTMT 00000000 WCHAR * 0x0013C274 (NYI) SDWORD * 0x0013C2C0 WCHAR * 0x02A7DC7C SWORD 4009 SWORD * 0x0013C2AC Licensstyring 1748-1758 EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND) HENV 07F11540 HDBC 07F115E8 HSTMT 00000000 WCHAR * 0x0013C274 (NYI) SDWORD * 0x0013C2C0 WCHAR * 0x02A7DC7C SWORD 4009 SWORD * 0x0013C2AC Licensstyring 1748-1758 ENTER SQLFreeConnect HDBC 07F115E8 Licensstyring 1748-1758 EXIT SQLFreeConnect with return code 0 (SQL_SUCCESS) HDBC 07F115E8 Licensstyring 1748-1758 ENTER SQLAllocConnect HENV 07F11540 HDBC * 0013C3B8 Licensstyring 1748-1758 EXIT SQLAllocConnect with return code 0 (SQL_SUCCESS) HENV 07F11540 HDBC * 0x0013C3B8 ( 0x07f115e8) Licensstyring 1748-1758 ENTER SQLSetConnectOption HDBC 07F115E8 SQLINTEGER 103 <SQL_LOGIN_TIMEOUT> SQLPOINTER 0x00000014 Licensstyring 1748-1758 EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS) HDBC 07F115E8 SQLINTEGER 103 <SQL_LOGIN_TIMEOUT> SQLPOINTER 0x00000014 (BADMEM) Licensstyring 1748-1758 ENTER SQLSetConnectAttrW SQLHDBC 07F115E8 SQLINTEGER 30002 <unknown> SQLPOINTER [Unknown attribute 30002] SQLINTEGER -3 Licensstyring 1748-1758 EXIT SQLSetConnectAttrW with return code 0 (SQL_SUCCESS) SQLHDBC 07F115E8 SQLINTEGER 30002 <unknown> SQLPOINTER [Unknown attribute 30002] SQLINTEGER -3 Licensstyring 1748-1758 ENTER SQLDriverConnectW HDBC 07F115E8 HWND 00750654 WCHAR * 0x74329A38 [ -3] "******\ 0" SWORD -3 WCHAR * 0x74329A38 SWORD 2 SWORD * 0x00000000 UWORD 3 <SQL_DRIVER_COMPLETE_REQUIRED> Licensstyring 1748-1758 EXIT SQLDriverConnectW with return code -1 (SQL_ERROR) HDBC 07F115E8 HWND 00750654 WCHAR * 0x74329A38 [ -3] "******\ 0" SWORD -3 WCHAR * 0x74329A38 SWORD 2 SWORD * 0x00000000 UWORD 3 <SQL_DRIVER_COMPLETE_REQUIRED> DIAG [HY000] [MySQL][ODBC 3.51 Driver]Could not determine the file name of setup library. (0) Licensstyring 1748-1758 ENTER SQLErrorW HENV 07F11540 HDBC 07F115E8 HSTMT 00000000 WCHAR * 0x0013C274 (NYI) SDWORD * 0x0013C2C0 WCHAR * 0x02A7DBD0 SWORD 4095 SWORD * 0x0013C2AC Licensstyring 1748-1758 EXIT SQLErrorW with return code 0 (SQL_SUCCESS) HENV 07F11540 HDBC 07F115E8 HSTMT 00000000 WCHAR * 0x0013C274 (NYI) SDWORD * 0x0013C2C0 (0) WCHAR * 0x02A7DBD0 [ 76] "[MySQL][ODBC 3.51 Driver]Could not determine the file name of setup library." SWORD 4095 SWORD * 0x0013C2AC (76) Licensstyring 1748-1758 ENTER SQLErrorW HENV 07F11540 HDBC 07F115E8 HSTMT 00000000 WCHAR * 0x0013C274 (NYI) SDWORD * 0x0013C2C0 WCHAR * 0x02A7DC7A SWORD 4010 SWORD * 0x0013C2AC Licensstyring 1748-1758 EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND) HENV 07F11540 HDBC 07F115E8 HSTMT 00000000 WCHAR * 0x0013C274 (NYI) SDWORD * 0x0013C2C0 WCHAR * 0x02A7DC7A SWORD 4010 SWORD * 0x0013C2AC Licensstyring 1748-1758 ENTER SQLFreeConnect HDBC 07F115E8 Licensstyring 1748-1758 EXIT SQLFreeConnect with return code 0 (SQL_SUCCESS) HDBC 07F115E8 Licensstyring 1748-1758 ENTER SQLFreeEnv HENV 07F11540 Licensstyring 1748-1758 EXIT SQLFreeEnv with return code 0 (SQL_SUCCESS) HENV 07F11540 How to repeat: 1) Create a new MS Access database. Create a linked table, select the system DSN just created--> Error: "ODBC--call failed. [MySQL][ODBC 3.51 Driver]Could not determine the driver name so could not lookup setup library.(#0)" 2) Then create a USER DSN. This works the first time when creating a linked table. When closing and then reopening Access, you get the following error: "ODBC--connection to 'xxx' failed. The database is located on the Internet. (MySQL Administrator works fine with the database; I can administer the database).