Connect to mysql with adodb

[jacek_arwal]

I tried to use Micrsoft ADO 6.0 library and connect with ado to mysql. Here is my code :



Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String

Set conn = New ADODB.Connection
conn.ConnectionString = "ODBC;Driver=MySQL ODBC 5.1 Driver;" & _ "SERVER=127.0.0.1;PORT=3306;DFLT_BIGINT_BIND_STR=1 ;" & _ "DATABASE=mybase;UID=myuser;PWD=mypwd"
conn.Open


But in this moment I receive an error - code -2147467259. This is tranlation from polish :
Generated by Microsoft OLE DB Provider for ODBC Drivers
Description [Microsoft][Driver manager ODBC] Can't find name of data source, and there is no preferred driver.

Mysql database is tunelled to localhost and I can read data in liked tables in access so connection string is good for 100% . I want to connect via ADO because I need to execute query that I cannot execute in linked table ( due to data conversion ).

[rpeare]

Does the table you want to update have a unique key (something like an autonumber in Access)? When you link the table are you setting the same field as the primary key? If you are you should be able to update the table from within access regardless of data type. I've done this with the linked table being a text field and the actual SQL table being a number field before. You just have to modify your update/add statement so the data types match.

I do not use MySQL specifically but I do use SQL.

[jacek_arwal]

In msaccess long data type is for numbers ranging in value from -2147483648 to 2147483647. In mysql unsigned long data type is for numbers ranging from 0 to 4294967295. Numbers greater than 2147483648 are displayed in msaccess as 2147483648 and thats why I want to execute query directly in mysql using ado.

[NoellaG]

Hi,

have you tried to get the connection string or connection object from the linked table (which works)?
You can get the link properties of linked tables through ADOX, for example:

Set cnn = CurrentProject.Connection
catMyDB.ActiveConnection = cnn
For Each tbl In catMyDB.Tables
If tbl.Type = "LINK" Then
For Each prp In tbl.Properties
If InStr(1, prp.Name, "Link Provider String") > 0 Then
'....
End If
If InStr(1, prp.Name, "Link Datasource") > 0 Then
'......
End If
Next prp
End If
Next tbl

gr
NG

[jacek_arwal]

Yes I have good connection string. I sometimes refresh connection with Refreshlink Method and this connection string and it works.