64-bit ADODB in Excel

[Maury Markowitz]

I'm having a devil of a time calling a database via an ODBC connection
that works perfectly in MSQuery via a DSN. I've tried every connection
string we could think of, including using a DSN=. No joy.

So perhaps this is a versioning issue? Is there a different Reference
I need to include if I'm using 64-bit? Both Excel and the ODBC driver
in question are 64-bit versions, but perhaps the ADODB I selected is
wrong?

I see 2.0 through 2.8, and 6.0. I tried with 2.6 and 6.0, but that
didn't help.

Frustrating it all is that the only error I get is the dreaded generic
"Automation Error".

 

[GS]

Maury Markowitz laid this down on his screen :

I'm having a devil of a time calling a database via an ODBC connection
that works perfectly in MSQuery via a DSN. I've tried every connection
string we could think of, including using a DSN=. No joy.

So perhaps this is a versioning issue? Is there a different Reference
I need to include if I'm using 64-bit? Both Excel and the ODBC driver
in question are 64-bit versions, but perhaps the ADODB I selected is
wrong?

I see 2.0 through 2.8, and 6.0. I tried with 2.6 and 6.0, but that
didn't help.

Frustrating it all is that the only error I get is the dreaded generic
"Automation Error".

This error suggests the problem is with your connection string
construct. I believe the 2.x ADODB libs are 32-bit and so I suspect the
6.0 version is 64-bit and what you should use.

Also, v12+ MSO apps use the "ACE" provider as its generic db engine.
Try switching to ACE with ref to ADODB 6.0!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

I forgot to mention that the 64-bit version of MSO works normally (in
x64) with SQL Server, but that's different than using ADODB <AFAIK>!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Maury Markowitz]

This error suggests the problem is with your connection string
construct.

Normally, but I've also seen it come back from all sorts of things,
from network errors to priv problems.

I believe the 2.x ADODB libs are 32-bit and so I suspect the
6.0 version is 64-bit and what you should use.

Can you think of a way to test this for sure?

Also, v12+ MSO apps use the "ACE" provider as its generic db engine.
Try switching to ACE with ref to ADODB 6.0!

The database in question is 64-bit Firebird. Is ACE like Jet, in that
you can use it as a meta-database to "reach through" to other data
sources?

 

[GS]

Maury Markowitz explained on 5/4/2012 :

Normally, but I've also seen it come back from all sorts of things,
from network errors to priv problems.


Can you think of a way to test this for sure?


The database in question is 64-bit Firebird. Is ACE like Jet, in that
you can use it as a meta-database to "reach through" to other data
sources?

ACe has replaced JET. I can't say if it even works in x64 because I
don't use x64 Excel (yet). My usual sources don't know either, but do
confirm that SQL Server queries work as normal (IOW, MS didn't break
that). If you can verify you're using ADODB x64 engine then you should
get some results. Otherwise, try finding documentation by anyone having
experience with x64 MS Office. I would expect that whatever works for
Access should also work for Excel as far as what's exposed to VBA goes.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion