Solved: MISSING: Microsoft ActiveX Data Objects Record 2.8 Library

[dkj]

As a newbie that has inherited the further customization of an application I have been experiencing an unusual problem.

I have an EXCEL VBA application (Excel 2002) that sends data to a SQLSERVER which works most of the time.

I have gone into References and selected "Microsoft ActiveX Data Objects Recordset 2.7 Library"

Every now and again I recieve my Excel application back from a user and when I try to run the application and load the SQLSERVER I get an error. When I check the References I find "MISSING: Microsoft ActiveX Data Objects Recordset 2.8 Library"

To resolve the problem I uncheck the MISSING:... option, close the References drop down, re-open the references and check what I had originally checked. "Microsoft ActiveX Data Objects Recordset 2.7 Library"

Is there a way of putting something in the code to prevent this from happening of does every one of the users have to be running the same version of Excel?

I know that this is somewhat long winded but I have spent considerable time looking for solutions in the many different forums and through textbooks.

The connection code looks like this:

[vba]'==================== DATABASE CONNECTION =============
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim lngRecsAff As Long
' now lets try to make the database connection here now
Set cn = New ADODB.Connection
cn.Open dbConnString
Set rs = New ADODB.Recordset
cn.Execute sqlInsStr, lngRecsAff, adExecuteNoRecords

Dim strCustomerReferenceNumber As String

strCustomerReferenceNumber = Replace(Sheet1.Cells(4, 5), "'", "''")

' now lets get the primary key
strSQL = "SELECT customerID FROM ipOppCustomer where customerReferenceNumber = '" & strCustomerReferenceNumber & "'"

rs.Open strSQL, cn
Sheet1.Cells(222, 2) = rs("customerID")
rs.Close
' now close the connection
cn.Close
Set cn = Nothing
'==================== END DATABASE CONNECTION ======[/vba]

[Ken Puls]

Hi there,

Nope, don't have to be running the same Excel version, but you have to code for that possibility. A later version automatically upgrades the references on you, which gives you the issue going back.

Check these out, as they may be helpful:
Add a VBA Reference Library via code
Remove Missing VBA Library References via code

Cheers!

[JonPeltier]

Trying to set references in code opens an extra can of worms, with additional security levels, and more opportunities for problems.

The real solution to this problem is to use a multi-file approach. The code goes into an add-in; each user installs the add-in, and if any updated references are present, they update on that user's computer without screwing up anyone else's. All the data is stored in another workbook; when you receive a workbook from another user, there's no code and no references in the workbook to cause problems.

[Ken Puls]

Hey Jon,

That's a different approach than I've used in the past, but I'd agree that it would be the preferable one. I does take a little more groundwork and organization to set up, though, but ultimately should avoid this problem entirely.

Darnit all though... I was quite proud of those entries!

[JonPeltier]

Ken -

I spent weeks with one client trying to incorporate a similar system into his existing solution, which was based on a single workbook that contained all the code. His program used Solver, which is among the least friendly add-ins to interact with. The references crashed half the time. We ended up using Application.Run to interact with Solver.

If I could have talked him into separating the data part from the program part, and distribute an add-in and a template, we could have made solver work much better. A two-part system is much more flexible, and much easier to update. How do you update just the code in a workbook that has code, data, and formatting all integrally merged? It's next to impossible. In a multi-part system, you change one part, and the other parts remain intact.

In fact, a three part system is even better: program, data, and presentation. The whole XML thing is about keeping the data separate from the presentation.

[Bob Phillips]

In fact, a three part system is even better: program, data, and presentation. The whole XML thing is about keeping the data separate from the presentation.

Before that, client-server also had that as an objective. Then the three level paradigm, client (presentation), application (business logic), and server (data management) is the right way to go IMO. You can always hybrid with valid, considered (and documented) reasons.

[Ken Puls]

Great feedback, guys, and I do appreciate it. It's also an approach that's endorsed by Bullen, Bovey & Green in their Professional Application Development book. That one is a bit of a slow read for me now, but I do see the value here.

Thanks guys!

[dkj]

I still had problems with setting the references but realized after some research that the solution was, if I was to keep the application intact, was to remove the reference altogether and late bind.

Being from 'Old School' I would have prefereed to separate, application, code and data but given my timelines that wasn't an option this time.

Thank you for all of your input.