Hello.
I want to read access db from within SQL Server 64-bit. On 32-bit, I can do:
SELECT f1, f2 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'd:\db.mdb';'admin';'',SomeTableOrQuery)
This fails on 64-bit because there are no jet oledb for 64-bit.
I need to do this, and I suspect I am not the only one.
!! Please compile a 64-bit Jet oledb provider !!
For now, I will try to query through a 32-bit SQL Server (express) on the same machine, it might work, but is a card-house of a solution.
How about a generic OLEDB32 provider I could write my query like
SELECT f1, f2 FROM OPENROWSET(
'Microsoft.Generic32.1.0',
'Jet.OLEDB.4.0\d:\db.mdb';'admin';'',SomeTableOrQuery)
This, if possible, would seriously easen the transition we all will have to do to 64-bit, I think.
Or will I need to do this myself (I feel so alone).
OLEDB 64, 32 (SQL 2005/64 / Jet)
SlashEMc2k
> Are you getting this issue with J# or with some other language
Sorry. Wrong forum. Please redirect/move my post...
Language is SQL.
Problem is (kind of) solved.
http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-200564.html
I will try to explain.
Background
I use SQL Server/64 as an "integration server", only. I have several SSIS packages that every night needs to get some data from an Access db (together with data from UNIX FTP, SQL Server and RapidFile). I am a big fan of the "Control Flow" in SSIS and a somewhat lesser fan of "Data Flow" (still a fan) I want to do as much as possible in the SQL Server memory space.
Almost everything I do in SSIS is duplicated as Stored Procedures for SQL Server. It all ends with a CLR SP that converts the data to a special format I use for my AJAX-server (http://gorm-braarvig.blogspot.com/2005/10/web-database-server-oh-yeah-database.html)
Problem
I can not use 'Microsoft.Jet.OLEDB.4.0' from 64-bit SQL Server.
Solution
Running distributed queries through SQL Server/32 (Express was fine)
Better soultions
- A generic 64-bit provider that uses 32-bit providers is a solution to this problem, I think. Only, it does not (to my knowledge exist)
- A Microsoft.Jet.OLEDB provider would help in this particular instance. However, wouldn't it be nice to be able to use, say OLEDB provider for ODBC through a generic provider
Payback
Just wanted to feel your scenario better ...
Are you getting this issue with J# or with some other language.
Thanks.
HLD77
Good to know that you got the solution and thanks for posting the solution as well.
Thanks.