Quick Tip: VBA ADO Connection Run-time error '3709'

  • Article

David Meego - Click for blog homepageIt has been a long time since I shared a Quick Tip for Visual Basic for Applications (VBA), but I have had this one waiting for me to write up for a while.

When using VBA on a window with ActiveX Data Object (ADO) to connect to SQL Server to read and/or write data to tables, the best practice is to open the ADO connection on the Window_BeforeOpen() event and to close the ADO connection using the Window_AfterClose() event.

This is similar to the best practice for reports discussed in the Using ADO with VBA with Report Writer post.

There is one big difference in behaviour between windows and reports that can cause problems with this technique.... cue dramatic music.....

Windows have a user interface which might cause a dialog to open and the window closure to be aborted.

 

OK, big deal, why should this cause a problem for us?

Well, let's work with the following scenario:

  1. The user opens the window and the Window_BeforeOpen() event opens the ADO connection.
     
  2. The user is working in the window and the VBA code is reading and writing via ADO as desired.
     
  3. The user is has finished editing data in the window (but has not saved) and closes the window.
     
  4. The "Do you want to Save, Discard or Cancel?" dialog opens and the user decides to cancel.
     
  5. The window closure is aborted and the window stays open.
     
  6. The user continues working and the next time VBA attempts to use ADO, the following error shows up: 

Run=time error '3709':

Requested operation requires an OLE DB Session object, which is not supported by the current provider.

So what happened?

The issue here is that while the Dexterity WIN_POST script aborted the window closure, the script itself as still executed. Therefore the VBA Window_AfterClose() script also executed... and closed the ADO connection even though the window remained open.

 

The solution to this issue is to make sure that the Window_AfterClose() script does not close the ADO connection if Cancel was selected on the pop up dialog.

Please see the Knowledge Base (KB) Article below for details of the solution including example scripts:

 

Hope you find this one useful.

David