DAO importing doesn't work

Hello, I'm trying to get a macro in Excel to import table from Access.

I have this working fully in ADO, but when I try DAO I keep getting a runtime error at the open database part and cannot see why.

Please Login or Register  to view this content.

I have ticked 'Microsoft DAO 3.6 Object Library'

I don't get a message, it just shows an error box
error.png

It fails at the OpenDatabase line.

Please Login or Register  to view this content.

I haven't touched the error trapping, so it's on the default 'Break on Unhandled Errors'

Are you running 64-bit?

Windows 7 is 64 bit. Office is 32 bit

I changed to break on all errors.

I get a runtime error 3343. Unrecognised database format.
Why do I get this, it's just a standard Access database?

How odd. Everywhere I went to online said to use the Microsoft DAO 3.6 Object Library.

After a quick google of that new error message I've now changed the reference to 'Microsoft Office 14.0 Access database engine Object Library' and it now works. Thank you

Why do you want to use DAO if you've got it working in ADO?

I'm learning.
My macro also takes quite a while so I was also curious to know if there were any speed differences so I could pick the fastest one.

Not looping a range to populate it will improve the performance far more than trying to swap ADO for DAO

Yes, why can't you just use conditional formatting?

Looping through a worksheet is very, very slow and should be kept to a minimum, especially if you have a lot of data. If you want an intermediate step, you could use GetRows and manipulate the data in an array - then write that to the sheet in a single pass

This is my function that colours the cells:

Please Login or Register  to view this content.

Out of the 2 tables that I import, the first table contains the values (mostly dates), the other table contains true or false, each have their own colour code system.
I don't think doing this is possible via conditional formatting.

Why don't you just do a join?

My excel file is stored on a public folder where many employees can view it.
I have made my Access DB to use various make table queries onto another Access DB that is also stored on the the public folder. The excel file imports from the DB in the public folder.

I'm still learning about access so I don't know how get my make table queries onto another DB that include joins.

And to actually get the data setup ready for the make table queries requires several other select and crosstab queries.
There very well may be a better way compared to what I've got going, but I don't know, and it's taken me months to get to this stage.