ODBC connnection call fails

[peekay]

I have tested the ODBC DSN and it works but the following code statement fails with error: ODBC - call failed:

Dim Conn As Connection
Set Conn = wsODBC.OpenConnection("Conn1", , , "ODBC;DSN=IBSMSbloem;")

What can be the problem?

Thanks

PK Odendaal

 

[dilettante]

Not enough information. Did you define it as a 64-bit DSN?

In any case DSNs are generally a bad idea, especially system DSNs. Just put the details into the connection string instead.

 

[BasicBoy]

dilettante,
Thanks for your reply, which I always regard highly.
I do not know how to establish whether it is a 32 or 64 bit DSN and what it should be. I am using vb6 with MySQL.
How would I specify the DSN in the connection object if it is not registered under the System DSN? If you could help me getting the connection string right I will appreciate.
Thanks.

 

[strongm]

If you downloaded the driver after following my link in thread222-1787819 then it is 64bit

 

[BasicBoy]

It is a 64 bit driver.

 

[dpaulson]

I could not get the 64 bit drivers to work. I installed an older 5.3 32 bit driver and that works. The connector string that I use is

strConnect = "Driver={MySQL ODBC 5.3 ANSI Driver};Server=YourServerIP;Port=3306;Database=YourDatabase;User=YourUserName;Password=YourUserPass;Option=3;

David Paulson

 

[BasicBoy]

Hi David,
I think you are right and thanks so much for the tip.

I found this excellent link for installing and registering the myodbc5a.dll file (for ANSI)in the correct folder:

https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-installation-binary-windows-dll.html

I did register it - see attachment 01, but I am still unable to use the 32 bit driver due to error code 193.
This link explains error code 193, but I seem unable to solve the error: (see attachment on next message)

http://www-01.ibm.com/support/docview.wss?uid=swg21377507

Any further help will be appreciated.
Thanks.

 

[BasicBoy]

Attachment 02

 

[dpaulson]

What OS are you using? I don't set up a system DSN, As long as the driver is listed in the ODBC Data Source administrator (32 bit) you can use it.
Just substitute your information in the connection string and it should work.

Public Conn As Connection
Public strConnect as string
Set Conn = New Connection
Conn.CursorLocation = adUseClient
strConnect = "Driver={MySQL ODBC 5.3 ANSI Driver};Server=YourServerIP;Port=3306;Database=YourDatabase;User=YourUserName;Password=YourUserPass;Option=3;"
Conn.Open strConnect

David Paulson

 

[BasicBoy]

Hi David,
I am on Windows 7
I think you are using VB.NET or ADO. I am trying to use DAO.
Kind regards.

 

[dpaulson]

I'm on Win 10, but I have machines on Win 7 , and xp accessing the same Mysql database. I am using VB6 and ADO.

David Paulson

 

[dilettante]

You must define the DSN using the 32-bit ODBC Administrator. On a 64-bit system people often run the 64-bit tool instead. That results in a DSN that 32-bit applications can't use because it is in the wrong format and points to a 64-bit ODBC Driver.

ODBC Administrator tool displays both the 32-bit and the 64-bit user DSNs in a 64-bit version of Windows

To work around this problem, use the appropriate version of the ODBC Administrator tool. If you build and then run an application as a 32-bit application on a 64-bit operating system, you must create the ODBC data source by using the ODBC Administrator tool in %windir%\SysWOW64\odbcad32.exe. To indicate the type of DSN, you can add "_32" to the 32-bit user DSNs and "_64" to the 64-bit user DSNs.

 

[peekay]

Thanks David and dilettante,

Four issues I need to pursue.

1. The link of dilettante is insightful and I will go through that process. Must I presume that the reference to 32 bit is to VB6 or to the database?
2. David gave and ADO solution which I will test. However, I need a DAO one. As far as I know there is no real data handling differences between the two other than a few ones of speed, connection, personal choice and support. In my view if ADO works, then DAO should also work.
3. There is somewhere a reference that one can work through Access links to MySQL. Is this a worthwhile transition or should I cut our the intermediate Access links and work directly to MySQL. It may take me months to change my program to work directly with MySQL and I thought that in the meantime I could work through Access.
4. Is it better to work with PHP or JSON and Apache indirectly changing the database or is connecting directly to the DSN better or easier.

Thanks

PK Odendaal