Difference between revisions of "MS Access and MySQL/connecting"
m (→Notes: index bad for non-sortable field types, that is) |
(→Notes: more problems) |
||
| Line 14: | Line 14: | ||
|} | |} | ||
(I'll add to this table as more examples come up.) | (I'll add to this table as more examples come up.) | ||
| + | |||
| + | Access also has trouble dealing with AUTOINCREMENT fields (usually used for ID) in the table data viewer. If you create a new field but leave the autonumbered ID field blank, on exiting the field Access will show the record as "''#deleted''". If you close and reopen the table data view, the new data appears. If you enter an ID by hand when you create the record, this problem doesn't happen. | ||
| + | |||
| + | Also, I'm currently having trouble writing to any records via [[DAO]]. On "Update", an error message states that another user is trying to write to the same data (which, as far as I can tell, is simply not true; for one thing, I can edit the same record in the [[MySQL Query Browser]] on the server side). It may be that this reflects a bug in ODBC. Maybe I should try [[ADO]] instead of [[DAO]], but I'm hesitant to start down that path since it involves a lot of code changes and I have generally run into problems (limitations) with ADO under VB6. | ||
Revision as of 21:48, 14 November 2005
It is possible to use Microsoft Access as a client – or as an intermediary for Visual Basic code – while using MySQL as the database engine. This provides a somewhat less bumpy migration path for VB/VBA applications.
The basic technique is to set up your MySQL server as a data source via ODBC. (I'll post more about that later, but it's fairly intuitive. Under Windows 98, ODBC data sources are created and configured with the "ODBC Data Sources (32 bit)" in the Windows Control Panel; the process should be similar for later versions of Windows. Once you've created a source, you link to its tables with Access.)
Notes
ODBC appears to translate data types it doesn't understand into Memo fields. This would be fine, except that you can't sort on Memo fields – so you may have to use types which ODBC can handle better. Fields which can't be sorted on also can't be designated as indexes in the data schema (on MySQL's end), or Access will refuse to import the table.
| MySQL type | MS Access type | Notes |
|---|---|---|
| TINYTEXT | Memo | |
| VARCHAR(255) | Text | field size = 255 |
(I'll add to this table as more examples come up.)
Access also has trouble dealing with AUTOINCREMENT fields (usually used for ID) in the table data viewer. If you create a new field but leave the autonumbered ID field blank, on exiting the field Access will show the record as "#deleted". If you close and reopen the table data view, the new data appears. If you enter an ID by hand when you create the record, this problem doesn't happen.
Also, I'm currently having trouble writing to any records via DAO. On "Update", an error message states that another user is trying to write to the same data (which, as far as I can tell, is simply not true; for one thing, I can edit the same record in the MySQL Query Browser on the server side). It may be that this reflects a bug in ODBC. Maybe I should try ADO instead of DAO, but I'm hesitant to start down that path since it involves a lot of code changes and I have generally run into problems (limitations) with ADO under VB6.