User Type Not Defined

[AlexanderBB]

I'm trying to open a DAO connection to an .accdb file (created in Office 2019) with excel 2010.
I'm told its Unrecognized database format.
Googling for a solution finds
Changing from Microsoft Office 3.6 Access Database Engine Object Library to Microsoft Office 12.0 Access database engine Object fixed it for me.

I had a reference Microsoft DAO 3.6 Object Library (which I hope he was referring to) and removed it. In Access 2019 there's Microsoft Office 16.0 Access database engine Object with a filename of ACEDAO.DLL. I copied this into the Windows/Sytem32 folder, then browsed and selected it from Excels References dialog. But it does not add itself to the list of refernces and Excel reported DAO is User type not Defined.
Is there anything else I can do/try to make this work ?

 

[rlv01]

I believe that the Microsoft DAO 3.6 Object Library was 32 bit only. Support for 64-bit DAO (JET database engine) is missing from 64-bit Microsoft Office, but it turns out that there is another engine (ACE engine) which appears to have all the same calls as DAO 3.6.

The replacement reference is: 'Microsoft Office 16.0 Access database engine Object Library'. You should not have to copy it to Windows/Sytem32 and I doubt it would work there. If Office is on your PC it should just show up in the reference list. The location on my PC is

C:\Program Files\Common Files\Microsoft Shared\OFFICE16\ACEDAO.DLL

So far all of my old DAO code now works again under 64bit.

 

[AlexanderBB]

Both Access 2019 and Excel 2010 are 32 bit.
The following works but ONLY if file is .mdb

cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.12.0; Data Source=O:\TableJun.accdb"
cnn.Open  '<<<<< The error is on this line.

If it's .accdb it tells me
Run-time error '3706':
Provider cannot be found. It may not be properly installed.
I tried copying Office 16 ACEDAO.DLL to both System32 and Microsoft Shared\OFFICE16\ but it won't add itself to the list of references.
Maybe it needs to be registered? This is all a bit beyond me so any help appreciated

 

[rlv01]

Sorry, I don't have any other insights. It all just works on my setup.

 

[RoryA]

It looks like you are using ADO, not DAO, so you should be using the ACE.OLDEB.12.0 provider.

 

[AlexanderBB]

Rory, *How* do i use it ? I tried ADO and it told me wrong datyabse format, and DAO gives the provide eror.
Is this somethibg there;'s no known solution for> If so I'll go to cut and paste!

 

[RoryA]

Change the provider in the connection string you posted.

 

[AlexanderBB]

Hey that's cool Rory, it works! I didn't realise what you first meant. Itll save me a heap of time.
Thanks