Difference between revisions of "MS Access and MySQL/connecting"
(→Notes: solved table-locking problem) |
(→Problems Solved: another variation) |
||
Line 24: | Line 24: | ||
[[Image:MS Access error 3197.png]] | [[Image:MS Access error 3197.png]] | ||
− | + | This happens under at least two different circumstances. | |
+ | |||
+ | In one of them. the code was opening a new recordset each time, which apparently I could get away with when the database was local. I also added a timestamp field to the table as recommended [http://forums.mysql.com/read.php?65,43009,44747#msg-44747 here]; it remains to be seen if that was necessary. | ||
+ | |||
+ | In another, I couldn't figure out why the message was occurring, but the data ''was'' actually being written properly, so I just inserted an | ||
+ | On Error Resume Next | ||
+ | ... | ||
+ | On Error Goto 0 | ||
+ | pair around the line where the error was occurring. |
Revision as of 22:52, 5 November 2006
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. Then, in MS Access, you link to the tables you want in the ODBC data source.
Instructions
- adding an ODBC data source in Windows 98 (the process should be similar for later versions of Windows)
- connecting to an ODBC data source in MS Access 97 (the process should be similar for later versions of Windows and MS 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.
Problems Solved
I ran into a problem writing records using DAO. On "Update", an error message stated that another user is trying to write to the same data. The error message:
This happens under at least two different circumstances.
In one of them. the code was opening a new recordset each time, which apparently I could get away with when the database was local. I also added a timestamp field to the table as recommended here; it remains to be seen if that was necessary.
In another, I couldn't figure out why the message was occurring, but the data was actually being written properly, so I just inserted an
On Error Resume Next ... On Error Goto 0
pair around the line where the error was occurring.