ADODB Connection object causes access to crash

[Rank Am]

Hi,
I have this intermittent but highly irritating issue that causes access to crash - the untrappable and generic "Access has stopped working" I haven't found much on msdn forums or here. I was wondering if anyone else has had this and how they permanently fixed it. The system is set up using ADODB to handle data transfer from the server to the frontends for several reasons one being we have major VPN bandwidth issues to the remote sites so the Jet / Linked table option caused headaches.
I have tracked the error down to instancing the ADODB Connection object and it occurs regardless of whether the connection is implicit (e.g. currentproject.connection) or when creating a separate connection object and explicitly stating the connection string, provider, cursor location etc.
The only way to fix it is to run the diagnostics which reports that all is good and that no changes were made and then it mysteriously works again for a while then the same happens.
I have tracked the occurrence down to machines running windows 7 and with office 2010 products (such as visio or project) along side our standard office 2007 and after an update installation has been made of 2010 products. I think it must have something to do with references being mucked up between Office 12 and 14 and the inability of programs in Win 7 to make registry changes so 2007 starts trying to access the 2010 library any body know for sure? any easy way to stop this?

 

[mdlueck]

I am making extensive use out of ADO type connections to a SQL Server 2008 R2 BE DB to run Stored Procedures via ADO Command objects and ADO RecordSet objects to run SELECT queries.

I have had no trouble with the ADO Connection objects, OTHER than when SQL Server was having a bit of performance troubles. Those troubles have been resolved, and the ADO Connection objects are not having trouble any longer.

The way I use them is at FE application startup, once the app is up far enough that all of the information needed to make the ADO Connection object, THEN it is made. I have a special class which has FE DB global visibility holding that special object. Other class instances which need to connect to the BE DB request a reference to the Connection object via asking the object for a reference to it. Those DB classes then use that to run the transaction. At the end of the method which is running some transaction, the adoCMD / adoRS objects are always destroyed. So the only thing which remains the entire time the application is up in the one ADO Connection object.

How does this compare to how your application is making use of ADO Connection objects?

 

[Rank Am]

This may be of some use to those who encounter ADO Connection Object instancing issues
I managed to track down the error yesterday and am pretty sure that it is Visio 2010 on the same client machines as the 2007 access front end using ADO connections that's causing the problem.
I installed and updated Visio 2010 on both Win XP SP3 and Win 7 machines both on our Domain and a non networked machine. The fatal Access 2007 Error occured on all machines (accdb/e mdb/e adp etc didn't matter) after installing Visio 2010 - regsnap indicates that Visio 2010 updates many office dll's including path changes to office 14 for some access specific dlls , running the office diagnostics tool from 2007 changes them all back to office 12 - then when Visio 2010 is launched again the installer launches and changes them back to office 14 and instancing an ADODB Connection object caused access 2007 to crash. This cannot be intended behavior so I can only assume its a bug.
When I knew what I was looking for I found others reporting similar issues on MSDN, Even typing ?currentproject.connection in the immediate window caused the fatal error and access to shut itself down
http://social.technet.microsoft.com.../thread/01cc51c4-68c7-4e41-a1c3-19a15671c2cd/

This may affect other office 2010 programs and other dll calls I don't know- I didn't go any further, according to IT the few users who need Visio can use 2007 under our license which I also tested no issues, and issued a FE patch which detects office 14 products installed on the local machine and warns the user - problem solved.

Mdlueck: I use ADODB Connection Objects in a similar way to you; but we don't maintain connections these are created and destroyed usually before the recordset, i.e. a disconnected recordset, as needed. The main reason for this is the remote nature of many of our users (bandwidth issues and poor network stability). Also it is a simple matter for ADODB connection Objects to query LDAP and use the domain schema to control permissions, log updates/edits etc The ADO connection object is ideal for use with the active directory. This is more difficult or impossible with DAO / Jet. Also we have other dept's that need some of our data to be read from Oracle etc The ADO connection object makes this easy.
We are running on Jet Backends at the moment but are planning to migrate all the backends (apart from local caches) to SQL server this year another reason to use ADO rather than DAO.
All our frontend ADO code is in a seperate accde file that only contains classes and modules and used as reference library for the front end application.
having said all that if we were just transferring data behind an office LAN to a jet backend and other office frontends I would use DAO, it was designed with jet in mind and issues with access sql functions not working in SQL statements with ADO, setting custom jet properties supporting mdb group and user permissions etc and many others simplify the use with access.
Funny how its took Microsoft years to realise that ADO is never going to be a replacement for DAO - they obviously never use their own products.....haha

 

[mdlueck]

I am glad you got the trouble worked out. Thanks for the heads up about mixing Office 2007 / 2010 components.

The one place I make use of DAO objects is in double/nested DAO.QueryDef objects which send Pass-Through SQL to the BE DB and ultimately download records to an FE temp table so I may populate a multiple records form as read-only. Everything else I accomplish with ADO objects. Seems to me that both ADO and DAO are necessary in Access.