Code update for 32bit Machine - OLEDB

[DiscoPistol]

Morning all,

Just after a bit of advice about an unusual problem I've encountered.

I've been asked to build a database for another organisation with the proviso that the Front end and Back end are separate.

I've managed to do this but I've had to build in 64 bit (as I have a 64bit machine and 64 bit Office) but I have an inkling that the other organisation will be on 32bit

The code I have looks like this

Set Cn = CreateObject("ADODB.Connection")
With Cn
    .Provider = "Microsoft.Ace.OLEDB.12.0"
    .ConnectionString = "Data Source=" & database_path
End With
Cn.Open

I do have some machines with 32bit Runtime on but the above code (not surprisingly) doesn't work and my revised code (see below) doesn't work either. Of course its Runtime so I have no idea why.

Set Cn = CreateObject("ADODB.Connection")
With Cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & database_path
End With
Cn.Open

My question is, am I missing something obvious? Will the code never run in Runtime for instance or have I missed something out?

I don't usually dabble in Access so any ideas would be welcome.

Thanks

DP

 

[Micron]

I must be dangerous because I only have a little bit on knowledge on this subject (and you know what they say about a little bit of knowledge)
So what I think you've posted is the 32 and 64 bit versions of the connection string, but you need to have the correct driver installed. You may be using the old one (note: every attempt I make to provide the url for this topic gets converted to the download page for the driver, which I've never visited -very odd. The link is also in the last link of my post, so best you get it from there).

I don't know if the 64 bit version is backwards compatible (and if so, can you install and use that driver on a 32 bit machine?). If not, you may need to detect which version is running on a machine so you can direct flow based on the environment:

#If Vba7 Then 
Declare PtrSafe Sub... 
#Else 
Declare Sub... 
#EndIf

Google prtSafe for more info. Here's more info on the bit version differences
ms access - Difference between Microsoft.Jet.OleDb and Microsoft.Ace.OleDb - Stack Overflow

and info on the use of the driver
excel - Working with OLEDB connection on Office 2013 64 bit - Stack Overflow

 

[DiscoPistol]

On this occasion I think a little bit on knowledge on this subject is a wonderful thing!

Really appreciate your help and I'll look into the driver issue.

I actually got the other organisation to try it yesterday and it worked (huzzah!) but I have no idea if this was because they are also 64bit or because the 64bit is backwards compatibles.

The search goes on!

Thanks again

Dave

 

[Norie]

Dave

As far as I can see there's nothing in your code that needs to be changed for 32bit.

The connection string, and in particular the provider, are dependent on the version of Excel being used, not the OS.

 

[DiscoPistol]

Thanks Norrie.

As I suspected but it's nice to have a sanity check!

Dave