Difference between revisions of "MS Access and MySQL/connecting"
(→Notes: image of error dialog) |
(→Update error: sometimes, you just have to use SQL.) |
||
| (10 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| − | 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. | + | ==Overview== |
| + | 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|migration]] path for VB/VBA applications. | ||
| − | The basic technique is to set up your [[MySQL]] server as a data source via [[ODBC]]. | + | 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. |
| − | + | * [[adding an ODBC data source in Windows 98]] (the process should be similar for later versions of Windows) | |
| − | ODBC | + | * [[connecting to an ODBC data source in MS Access 97]] (the process should be similar for later versions of Windows and MS Access) |
| − | + | ==Problems Solved== | |
| − | + | ===Update error=== | |
| − | + | I ran into a problem writing records using [[DAO (Microsoft)|DAO]]. On "Update", an error message stated that another user is trying to write to the same data. The error message: | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | Access | + | [[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" pair around the line where the error was occurring: | ||
| + | <vb>On Error Resume Next | ||
| + | ...update the record... | ||
| + | On Error Goto 0</vb> | ||
| − | + | Another way to handle this, especially if there is any concern that the record is not always updating reliably, is to put use the following subroutine for updates: | |
| + | <vb>Public Sub UpdateRecord(iRec As Recordset) | ||
| + | On Error Resume Next | ||
| + | iRec.Update | ||
| + | If Err.Number = 3197 Then | ||
| + | ' do it again, and log the problem | ||
| + | iRec.Update | ||
| + | clsEventLog.LogEvent "UpdateRecord", iRec.Name, Err.Description, , True, False | ||
| + | End If | ||
| + | End Sub</vb> | ||
| + | This repeats the Update method (which is what the VB documentation recommends) and logs the problem. You will need to create your own logging code; clsEventLog is from the MS Access version of [[VbzCart]]. | ||
| − | + | '''Update 2009-04-11''': Sometimes, even this doesn't work, and even manually updating the data by opening a window for the table in Access causes a [[:Image:MS Access 97 data write conflict dialog.png|write conflict dialog]] to appear -- where none of the options will allow your changes to be written. Rebooting the Windows machine may or may not solve the problem; you may have to go into MySQL and manually update the record, or execute an "UPDATE" in SQL. | |
| − | [[ | + | ===FindFirst is slow=== |
| + | Opening an ODBC table as a [[DAO (Microsoft)|DAO]] recordset and then using FindFirst() on that recordset can be immensely slow, taking minutes or hours for larger datasets. (This would seem to indicate that FindFirst() is requesting records one at a time and checking each one for a match, rather than passing the filter request to the remote server or even reading the entire dataset into memory before searching it.) | ||
| + | |||
| + | The alternative, which turns out to be very quick, is to use a query to open a recordset containing just the record(s) you want, using CurrentDb.OpenRecordset(). Even if each record is created and opened separately, this is still much faster than Recordset.FindFirst(). | ||
Latest revision as of 15:25, 11 April 2009
Overview
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.
- 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)
Problems Solved
Update error
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" pair around the line where the error was occurring: <vb>On Error Resume Next ...update the record... On Error Goto 0</vb>
Another way to handle this, especially if there is any concern that the record is not always updating reliably, is to put use the following subroutine for updates: <vb>Public Sub UpdateRecord(iRec As Recordset)
On Error Resume Next
iRec.Update
If Err.Number = 3197 Then
' do it again, and log the problem
iRec.Update
clsEventLog.LogEvent "UpdateRecord", iRec.Name, Err.Description, , True, False
End If
End Sub</vb> This repeats the Update method (which is what the VB documentation recommends) and logs the problem. You will need to create your own logging code; clsEventLog is from the MS Access version of VbzCart.
Update 2009-04-11: Sometimes, even this doesn't work, and even manually updating the data by opening a window for the table in Access causes a write conflict dialog to appear -- where none of the options will allow your changes to be written. Rebooting the Windows machine may or may not solve the problem; you may have to go into MySQL and manually update the record, or execute an "UPDATE" in SQL.
FindFirst is slow
Opening an ODBC table as a DAO recordset and then using FindFirst() on that recordset can be immensely slow, taking minutes or hours for larger datasets. (This would seem to indicate that FindFirst() is requesting records one at a time and checking each one for a match, rather than passing the filter request to the remote server or even reading the entire dataset into memory before searching it.)
The alternative, which turns out to be very quick, is to use a query to open a recordset containing just the record(s) you want, using CurrentDb.OpenRecordset(). Even if each record is created and opened separately, this is still much faster than Recordset.FindFirst().