DAO360 in 2003 becomes what in 2010?

[jasmith4]

I have an Excel 2003 spreadsheet which communicates with an Access 2003 database, which works in Windows XP / Office 2003, and I'm trying to get it to work in Windows 7 / Office 2010. This is out in production, so ideally I'd like to make no changes to either one of these files.

The problem is the spreadsheet's reference to Microsoft DAO 3.6, which in the XP/2003 world is in C:\Program Files\Common Files\Microsoft Shared\DAO. But the W7/2010 world is quite different, I guess -- DAO360.dll, DAO350.dll and DAO2535.dll don't exist any more, so where did all the stuff in the DAO library go?

I've tried adding to the spreadsheet a reference to the Microsoft Access 14.0 Object Library, but that doesn't help -- it won't compile, because it still doesn't know what a Database object is!

Cross-posted here: http://www.mrexcel.com/forum/showthread.php?t=638447

 

[xenou]

I have a dao library here in my Win7 system:
C:\Program Files (x86)\Common Files\microsoft shared\DAO\dao360.dll

Though for setting a reference it appears in my references as simply Microsoft DAO 3.6 Library, as before.

 

[jasmith4]

If you click on that reference you'll see its location. The problem is that the default location of DAO360.dll has changed, from C:\Program Files\Common Files\Microsoft Shared\DAO to C:\Program Files (x86)\Common Files\Microsoft Shared\DAO. So when I bring over my spreadsheet that works fine in XP/2003, its reference to DAO360.dll in the former location is invalid in W7/2010. That means, just because of that reference, I have to maintain two versions of the spreadsheet.

I've implemented in InstallShield a rather clumsy solution which would make Microsoft techies cringe, namely finding the existing and registered DAO360.dll on the target system and copying it to C:\Program Files\Common Files\Microsoft Shared\DAO, creating that folder if it doesn't exist. It doesn't re-register the DLL there, but it's there so that my spreadsheet, installed via the InstallShield package, can find it and thus run. Is there really no better way?

 

[RoryA]

btw the default for Office 2010 is actually the Microsoft Office 14.0 Access Database Engine Objects library.
You could late bind DAO instead of using the reference?

 

[jasmith4]

I don't want to have to change the spreadsheet or the database in any way: code, references, nothing. But if changes must be made, I'd still like the same spreadsheet and database to work in both environments, XP/2003 and W7/2010.

FYI I tried creating a blank spreadsheet in XP/2003, adding the DAO 3.6 reference, and writing a Sub in ThisWorkbook with one line: one declaration: "Dim DB as Database". When I brought that over to W7/2010 it didn't compile, because the DAO 3.6 reference was "MISSING". I added a reference to Access 14.0, but it still didn't compile -- it didn't know what "Database" is. But when I browsed to C:\Program Files(x86)\Common Files\Microsoft Shared\DAO\dao360.dll. only then did it compile.

Can you tell me how to do that late binding?

 

[RoryA]

Microsoft Office 14.0 Access Database Engine Objects is not the same library as Microsoft Access 14, which is why it didn't compile. To late bind, you declare everything as object and then use CreateObject rather than New or default instancing. for example:

Dim dbe As Object
Set dbe = CreateObject("DAO.DBengine.36")

 

[jasmith4]

OK, thanks for those. But the bottom line seems to be that I have to maintain two different spreadsheets, because the reference to DAO 3.6 in XP/2003 isn't ever the same as in W7/2010, unless I stick with that awful InstallShield solution. Thoughts?

 

[RoryA]

If you late bind, there is no reference set - that's the only point of it.

 

[xenou]

^^ Right. Late binding should work under either XP or Win7 (the CreateObject function will resolve the library reference). In this case you do not set a reference under Tools | References, and dim all your late-bound objects generically as type Object. I do this normally after the code is written and tested since it easier to write the code with intellisense.