Quick Tip: VBA ADO Connection Run-time error ‘3709’

David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

It 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

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

3 thoughts on “Quick Tip: VBA ADO Connection Run-time error ‘3709’

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.