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:
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.
Great tip David!
I have actually run into this! So good to know the real answer.
Posting from Mark Polino at DynamicAccounting.net
PLEASE READ BEFORE POSTING
Please only post comments relating to the topic of this page.
If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.