Most programs should work with Connector/ODBC, but for each of those listed here, there are specific notes and tips to improve or enhance the way you work with Connector/ODBC and these applications.
With all applications, ensure that you are using the latest Connector/ODBC drivers, ODBC Manager and any supporting libraries and interfaces used by your application. For example, on Windows, using the latest version of Microsoft Data Access Components (MDAC) will improve the compatibility with ODBC in general, and with the Connector/ODBC driver.
The majority of Microsoft applications have been tested with Connector/ODBC, including Microsoft Office, Microsoft Access and the various programming languages supported within ASP and Microsoft Visual Studio.
To improve the integration between Microsoft Access and MySQL through Connector/ODBC:
For all versions of Access, enable the Connector/ODBC
Return matching rows option. For
Access 2.0, also enable the
column in all tables that you want to be able to update.
For maximum portability, do not use a length
specification in the column declaration (which is
unsupported within MySQL in versions earlier than 4.1).
Include a primary
key in each MySQL table you want to use with
Access. If not, new or updated rows may show up as
fields. Access fails when comparing with
single-precision floats. The symptom usually is that new
or updated rows may show up as
#DELETED# or that you cannot find or
If you are using Connector/ODBC to link to a table that
BIGINT column, the
results are displayed as
The work around solution is:
Have one more dummy column with
TIMESTAMP as the data
Change BIGINT columns to
INT option in the connection dialog in
ODBC DSN Administrator.
Delete the table link from Access and re-create it.
Old records may still display as
#DELETED#, but newly added/updated
records are displayed properly.
If you still get the error
Another user has
changed your data after adding a
TIMESTAMP column, the
following trick may help you:
Do not use a
table data sheet view.
Instead, create a form with the fields you want, and use
form data sheet view. Set the
DefaultValue property for the
TIMESTAMP column to
NOW(). Consider hiding
TIMESTAMP column from
view so your users are not confused.
In some cases, Access may generate SQL statements that
MySQL cannot understand. You can fix this by selecting
the Access menu.
If you have in Access a column defined as
BYTE, Access tries to export this as
TINYINT instead of
TINYINT UNSIGNED. This gives you
problems if you have values larger than 127 in the
If you have very large (long) tables in Access, it might
take a very long time to open them. Or you might run low
on virtual memory and eventually get an
Query Failed error and the table cannot open.
To deal with this, select the following options:
Return Matching Rows (2)
Allow BIG Results (8).
These add up to a value of 10
Some external articles and tips that may be useful when using Access, ODBC and Connector/ODBC:
Optimizing Access ODBC Applications
For a list of tools that can be used with Access and ODBC data sources, refer to http://www.mysql.com/portal/software/convertors/ section for list of available tools.
If you have problems importing data into Microsoft Excel, particularly numeric, date, and time values, this is probably because of a bug in Excel, where the column type of the source data is used to determine the data type when that data is inserted into a cell within the worksheet. The result is that Excel incorrectly identifies the content and this affects both the display format and the data when it is used within calculations.
To address this issue, use the
CONCAT() function in your
queries. The use of
forces Excel to treat the value as a string, which Excel
will then parse and usually correctly identify the embedded
However, even with this option, some data may be incorrectly
formatted, even though the source data remains unchanged.
Format Cells option within Excel
to change the format of the displayed information.
To be able to update a table, you must define a primary key for the table.
Visual Basic with ADO cannot handle big integers. This means
that some queries like
PROCESSLIST do not work properly. The fix is to
OPTION=16384 in the ODBC connect
string or to select the
Change BIGINT columns to
INT option in the Connector/ODBC connect screen.
You may also want to select the
If you have a
BIGINT in your
result, you may get the error
Driver Manager] Driver does not support this
parameter. Try selecting the
BIGINT columns to INT option in the Connector/ODBC
When you are coding with the ADO API and Connector/ODBC, you
need to pay attention to some default properties that aren't
supported by the MySQL server. For example, using the
CursorLocation Property as
adUseServer returns a result of –1
RecordCount Property. To have the
right value, you need to set this property to
adUseClient, as shown in the VB code
Dim myconn As New ADODB.Connection Dim myrs As New Recordset Dim mySQL As String Dim myrows As Long myconn.Open "DSN=MyODBCsample" mySQL = "SELECT * from user" myrs.Source = mySQL Set myrs.ActiveConnection = myconn myrs.CursorLocation = adUseClient myrs.Open myrows = myrs.RecordCount myrs.Close myconn.Close
Another workaround is to use a
COUNT(*) statement for a similar query to get the
correct row count.
To find the number of rows affected by a specific SQL
statement in ADO, use the
property in the ADO execute method. For more information on
the usage of execute method, refer to
For information, see ActiveX Data Objects(ADO) Frequently Asked Questions.
Return matching rows option in
For more information about how to access MySQL through ASP using Connector/ODBC, refer to the following articles:
A Frequently Asked Questions list for ASP can be found at http://support.microsoft.com/default.aspx?scid=/Support/ActiveServer/faq/data/adofaq.asp.
With all Borland applications where the Borland Database Engine (BDE) is used, follow these steps to improve compatibility:
Update to BDE 3.2 or newer.
Don't optimize column widths
option in the DSN.
Return matching rows option
in the DSN.
When you start a query, you can use the
Active property or the
Open method. Note that
Active starts by automatically issuing a
SELECT * FROM ... query. That may not be
a good thing if your tables are large.
Also, here is some potentially useful Delphi code that sets
up both an ODBC entry and a BDE entry for Connector/ODBC.
The BDE entry requires a BDE Alias Editor that is free at a
Delphi Super Page near you. (Thanks to Bryan Brunton
<firstname.lastname@example.org> for this):
fReg:= TRegistry.Create; fReg.OpenKey('\Software\ODBC\ODBC.INI\DocumentsFab', True); fReg.WriteString('Database', 'Documents'); fReg.WriteString('Description', ' '); fReg.WriteString('Driver', 'C:\WINNT\System32\myodbc.dll'); fReg.WriteString('Flag', '1'); fReg.WriteString('Password', ''); fReg.WriteString('Port', ' '); fReg.WriteString('Server', 'xmark'); fReg.WriteString('User', 'winuser'); fReg.OpenKey('\Software\ODBC\ODBC.INI\ODBC Data Sources', True); fReg.WriteString('DocumentsFab', 'MySQL'); fReg.CloseKey; fReg.Free; Memo1.Lines.Add('DATABASE NAME='); Memo1.Lines.Add('USER NAME='); Memo1.Lines.Add('ODBC DSN=DocumentsFab'); Memo1.Lines.Add('OPEN MODE=READ/WRITE'); Memo1.Lines.Add('BATCH COUNT=200'); Memo1.Lines.Add('LANGDRIVER='); Memo1.Lines.Add('MAX ROWS=-1'); Memo1.Lines.Add('SCHEMA CACHE DIR='); Memo1.Lines.Add('SCHEMA CACHE SIZE=8'); Memo1.Lines.Add('SCHEMA CACHE TIME=-1'); Memo1.Lines.Add('SQLPASSTHRU MODE=SHARED AUTOCOMMIT'); Memo1.Lines.Add('SQLQRYMODE='); Memo1.Lines.Add('ENABLE SCHEMA CACHE=FALSE'); Memo1.Lines.Add('ENABLE BCD=FALSE'); Memo1.Lines.Add('ROWSET SIZE=20'); Memo1.Lines.Add('BLOBS TO CACHE=64'); Memo1.Lines.Add('BLOB SIZE=32'); AliasEditor.Add('DocumentsFab','MySQL',Memo1.Lines);
The following information is taken from the ColdFusion documentation:
Use the following information to configure ColdFusion Server
for Linux to use the
unixODBC driver with
Connector/ODBC for MySQL data sources. You can download
ColdFusion version 4.5.1 lets you use the ColdFusion
Administrator to add the MySQL data source. However, the
driver is not included with ColdFusion version 4.5.1. Before
the MySQL driver appears in the ODBC data sources drop-down
list, build and copy the Connector/ODBC driver to
The Contrib directory contains the program
which lets you build and remove the DSN registry file for the
Connector/ODBC driver on ColdFusion applications.
For more information and guides on using ColdFusion and Connector/ODBC, see the following external sites: