To improve the integration between Microsoft Access and MySQL through Connector/ODBC:

Some external articles and tips that may be useful when using Access, ODBC and Connector/ODBC:

MySQL Enterprise.  MySQL Enterprise subscribers will find more information about using ODBC with Access in Knowledge Base articles such as Use MySQL-Specific Syntax with Microsoft Access. To subscribe to MySQL Enterprise see http://www.mysql.com/products/enterprise/advisors.html.

If you have problems importing data into Microsoft Excel, particularly numerical, 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 CONCAT() forces Excel to treat the value as a string, which Excel will then parse and usually correctly identify the embedded information.

However, even with this option, some data may be incorrectly formatted, even though the source data remains unchanged. Use the 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 SHOW PROCESSLIST do not work properly. The fix is to use 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 Return matching rows option.

MySQL Enterprise.  MySQL Enterprise subscribers can find a discussion about using VBA in the Knowledge Base article, MySQL-Specific Syntax with VBA. To subscribe to MySQL Enterprise see http://www.mysql.com/products/enterprise/advisors.html.

If you have a BIGINT in your result, you may get the error [Microsoft][ODBC Driver Manager] Driver does not support this parameter. Try selecting the Change BIGINT columns to INT option in the Connector/ODBC connect screen.

You should select the Don't optimize column widths option.

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 for the RecordCount Property. To have the right value, you need to set this property to adUseClient, as shown in the VB code here:

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 SELECT 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 RecordsAffected property in the ADO execute method. For more information on the usage of execute method, refer to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthcnnexecute.asp.

For information, see ActiveX Data Objects(ADO) Frequently Asked Questions.

You should select the Return matching rows option in the DSN.

For more information about how to access MySQL via 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.

Some articles that may help with Visual Basic and ASP:

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 <bryan@flesherfab.com> 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);

Tested with BDE 3.0. The only known problem is that when the table schema changes, query fields are not updated. BDE, however, does not seem to recognize primary keys, only the index named PRIMARY, although this has not been a problem.