MS Access and MySQL/connecting - Revision history

Woozle: /* Update error */ sometimes, you just have to use SQL.

2009-04-11T15:25:19Z

Update error: sometimes, you just have to use SQL.

← Older revision		Revision as of 15:25, 11 April 2009
Line 32:		Line 32:
	End Sub</vb>		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]].		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===		===FindFirst is slow===

Woozle: /* Update error */ subroutine code

2008-11-30T02:53:55Z

Update error: subroutine code

← Older revision		Revision as of 02:53, 30 November 2008
Line 17:		Line 17:

	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:		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:
	On Error Resume Next		<vb>On Error Resume Next
	...		...update the record...
	On Error Goto 0		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]].

	===FindFirst is slow===		===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.)		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().		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().

Woozle: layout tweaks

2008-11-09T01:40:08Z

layout tweaks

← Older revision		Revision as of 01:40, 9 November 2008
Line 3:		Line 3:

	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.		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)		* [[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)		* [[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===		==Problems Solved==
	===Update error===		===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:		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:

Woozle: moved data types over to "migration" page

2008-11-09T01:38:14Z

moved data types over to "migration" page

@@ Line 1: / Line 1: @@
-It is possible to use [[Microsoft Access]] as a client &ndash; or as an intermediary for [[Visual Basic]] code &ndash; while using [[MySQL]] as the database engine. This provides a somewhat less bumpy migration path for VB/VBA applications.
+==Overview==
 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.
@@ Line 5: / Line 6: @@
 * [[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===

Woozle: using MySQL with MS Access moved to MS Access and MySQL/connecting: reorganization

2008-11-09T01:00:54Z

using MySQL with MS Access moved to MS Access and MySQL/connecting: reorganization

← Older revision	Revision as of 01:00, 9 November 2008
(No difference)

Woozle: /* Instructions */ related pages: migrating Access to MySQL

2007-08-23T11:37:03Z

Instructions: related pages: migrating Access to MySQL

← Older revision		Revision as of 11:37, 23 August 2007
Line 5:		Line 5:
	* [[adding an ODBC data source in Windows 98]] (the process should be similar for later versions of Windows)		* [[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)		* [[connecting to an ODBC data source in MS Access 97]] (the process should be similar for later versions of Windows and MS Access)
			===Related Pages===
			* [[migrating MS Access to MySQL]]: moving data in MS Access over to MySQL, while dealing with the necessity of still accessing that data from MS Access during the process

	==Notes==		==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.		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.

Woozle: /* Problems Solved */ FindFirst is slow

2007-08-23T11:34:16Z

Problems Solved: FindFirst is slow

← Older revision		Revision as of 11:34, 23 August 2007
Line 20:		Line 20:

	===Problems Solved===		===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:		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:

Line 28:		Line 29:
	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 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		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:
	On Error Resume Next		On Error Resume Next
	...		...
	On Error Goto 0		On Error Goto 0
	~~pair around~~ the ~~line where~~ the ~~error was occurring~~.		===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().

Woozle: /* Problems Solved */ another variation

2006-11-05T22:52:34Z

Problems Solved: another variation

← Older revision		Revision as of 22:52, 5 November 2006
Line 24:		Line 24:
	[[Image:MS Access error 3197.png]]		[[Image:MS Access error 3197.png]]

	~~It turned out that this was because~~ 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.		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.

Woozle: /* Notes */ solved table-locking problem

2006-11-01T03:02:03Z

Notes: solved table-locking problem

← Older revision		Revision as of 03:02, 1 November 2006
Line 19:		Line 19:
	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.		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 (Microsoft)\|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 (Microsoft)\|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.		===Problems Solved===
			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:

	~~The~~ error ~~message:~~		[[Image:MS Access error 3197.png]]

	[~~[Image~~:~~MS Access error 3197~~.~~png]~~]		It turned out that this was because 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.

Woozle: fixed partial sentence

2006-10-31T19:30:09Z

fixed partial sentence

← Older revision		Revision as of 19:30, 31 October 2006
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.		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 tables in the ODBC data source ~~by selecting~~		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===		===Instructions===
	* [[adding an ODBC data source in Windows 98]] (the process should be similar for later versions of Windows)		* [[adding an ODBC data source in Windows 98]] (the process should be similar for later versions of Windows)