dowdrake
2011-07-21T21:41:05Z
Hi,

I recently came across a strange bug in Microsoft Access 2003 and 2007 which is related to iGrid. This bug produces an error message "ODBC Connection Failed" when attempting to update a record. It is fairly complex to reproduce, but you will likely encounter it at some point if you are using SQL Server as the back end and if your application uses unbound forms for editing data. Luckily there is a "Magic Fix" that is easy to implement! ;-)

All the details and sample code can be found here 

I haven't tested this with iGrid 4.7 yet, but will do so as soon as possible and post back.
Igor/10Tec
2011-07-22T13:57:08Z
Let's just add to this that iGrid's initialization code affects the internals of MS Access, but it shouldn't be so. In iGrid we use only pure VB6 code with legal WinAPI calls, so it is obviously a bug deep inside MS Access.
dowdrake
2011-07-22T16:49:21Z
In case it's helpful, I'm reposting my original article, with download here:

Access/SQL Server Glitch with Unbound Forms and iGrid - Magic Fix!

I've developed a number of applications in MS Access over the past several years that use unbound forms and the popular iGrid 3.0 activeX component. Several of these applications contain linked tables to a SQL 2005 database. I've recently discovered a bug in Access 2003 and 2007 that seems to be specific to this particular configuration. The bug results in an "ODBC Connection Failed" error which occurs after a recordset is opened and about to be placed in edit mode via 'rs.Edit'. Once this error occurs, all attempts to update the record will fail until the application is restarted.

Fortunately, I've found a "magic fix", which I'll explain further down. But first, here are the steps to reproduce this glitch.
  • Ensure that Office Professional 2003 or 2007 is installed
  • Ensure that the iGrid 3.0 Demo is installed (for our purposes, it doesn't matter if it is registered or not). I wasn't able to find a link to this version on the 10Tec site, but it is readily available for download from various mirror sites.
  • Ensure that there is a SQL Server instance accessible from your machine. I've only tested with SQL 2005, but the server version should not matter.
  • The following steps explain how to create an Access database for reproducing this error. If you prefer, you can skip these and download and modify the attached Access file (see below)
  • In Access create a new database with linked tables to a couple of tables in the SQL Server database. You can use a File DSN, a Machine (System or User) DSN, or a DSN-less link. It doesn't matter, but be sure to use the same type of DSN for all tables. Also, it is important that at least one of the tables is large -- i.e. contains a reasonable amount of data (say > 1 Mb). You can determine whether a table is large enough by opening it directly in Access. If the record count appears at the bottom when the table is opened, it's not large enough for our purposes.
  • Add two new unbound forms to your Access application.
  • Place an iGrid component on the first one. We'll call this the "View Form". You don't need to put any VB code behind this form.
  • Place a combo box and a button on the second one. We'll call this the "Edit Form" -- it will simulate a simple unbound edit form. Bind the combo box to one of the large tables, with format set to two columns and zero width for the first column. Add two event handlers, one for the form load event and one for the button click event.
  • In the VB code editor, add one or more variables with module scope. These will be used as intermediate storage.
  • In the form load event, open a recordset to read a single record from a table (it can be different from the one that the combo box is bound to). Copy some of the values into form variables, then close the recordset.
  • In the button click event, open a new recordset to read the same record as before, but this time, be sure to specify dbOpenDynaset and dbSeeChanges so that the record can be edited. Place the recordset in edit mode (rs.Edit), then set a couple of the column values. You don't really have to change the data in the table, you can simply set the column values to their current values (that you stored in form variables in the previous step). Then use rs.Update to save the record and close the recordset.


The attached MS Access 2002-2003 database AccessBug..zip is set up this way, with links to a couple of tables in the standard SQL Server 2005 AdventureWorks sample database.

Now, to reproduce the bug, follow this sequence.

  • Close and Re-open MS Access without opening the VB Editor
  • Open the view form ("FormWithGridControl" in the sample "AccessBug" database)
  • Open the edit form ("SalesEdit" in the sample "AccessBug" database)
  • Select the first value in the combo box on the edit form
  • Click the button on the edit form to "update" the record.
  • Close the edit form
  • Close the view form
  • Re-open the view form
  • Re-open the edit form
  • Again, select the first value in the combo box.
  • Again, click the button to "update" the record.


At this point you should see a message that the ODBC Connection Failed.

If you remove the iGrid control from the view form and repeat the above procedure, the error will not occur.
If you bind your combo box to a small table instead of a large one, the error will not occur.
If you give the recordset in the edit form module scope, open it in the form load event and keep it open until after the record is updated (as a bound form might do), the error will not occur.
If the table bound to the combo and the one being updated are using different DSN types, i.e. if one of the tables is linked via a Machine DSN and the other is linked via either a file DSN or is DSN-less, the error will not occur.
If before selecting a value in the combo box, you scroll it all the way to the bottom, the error will not occur.

And here's the "Magic Fix" you've been waiting for:

    Dim x As Long
    x = Me.Combo0.ListCount

Just put that in the form load event, and voila! the bug is fixed!!!

What's going on?

Well, it's really hard to say, since both MS Access and the iGrid control are black boxes to me, but my best guess is that it has something to do with the way Access manages memory. When you bind a table or view to a combo box, Access behaves differently depending on whether the source table is large or small. If it is small, the entire table is read at once into memory and all locks are removed. If it is large, however, only the first 1 MB or so is read into memory and locks remain in place. If then, the user scrolls the combo box to the bottom to force the rest of the table to be read into memory, the locks are removed. The "Magic Fix" above, does the same thing.

Clearly something in the iGrid initialization code, is triggering this Access bug, since the bug only occurs in the presence of an iGrid control. The iGrid developer, Igor Kvit, has assured me that this initialization code is a mix of pure VB6 code and legal API calls and I am confident that this is the case. I've tried a couple of other ActiveX components such as a calendar control, but was not able to reproduce the error. I haven't had time yet to check whether this bug can surface with the current version of iGrid (4.7), but plan to do so as soon as possible.
File Attachment(s):
AccessBug.zip (18kb) downloaded 58 time(s).
dowdrake
2011-07-23T21:02:08Z
I tested with the current iGrid version (4.70.0153) and was not able to reproduce this error using the demo version of the ocx, but only with the licensed version.

Edit: behavior was the same in Access 2003 and 2007