0

I see similar topics already posted but none gives me an answer. I am trying to import data from an Access database, not Excel.

Here is my query:

SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
              'Driver={Microsoft Access Driver (*.mdb)};
              Database=C:\Users\td\Dropbox\Utilization Reports\DB\201606\IMA_0216_v2k.mdb', 
              'select * from BackupLabels')

And here is the error message I get:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Could not find installable ISAM.".

I had the same error when linking to an Excel file. I used Regsvr32.exe to associate the .dll file using this site (https://support.microsoft.com/en-us/kb/209805) and that worked, but when I link to an Access database it doesn't. I tried to find a .dll file for Access but couldn't.

Does anyone have any idea? Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Todd181
  • 31
  • 1
  • 4
  • Your second argument to `OPENROWSET` looks wrong. It appears to be an ODBC connection string when, if I recall correctly, it should just be the database path and credentials (e.g., `..., 'C:\Users\td\Dropbox\Utilization Reports\DB\201606\IMA_0216_v2k.mdb';'admin';'', ...`). – Gord Thompson Jul 05 '16 at 22:21
  • Have you installed the appropriate Microsoft Access Database Engine 2010? 64 bit or 32 bit version? Are you attempting to import in a 32 bit process or 64 bit process? Are you potentially mixing up 32 & 64? – Matt Jul 05 '16 at 22:24
  • Oops I think ACE.OLEDB.12.0 is actually Access Database Engine 2007 according to wikipedia (https://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine) If you have 2010 installed but not 2007 you will have to uninstall 2010 install 2007 and then reinstall 2010+ the older must be installed prior to newer versions if you are going to use that provider. Otherwise try the new providers e.g ACE 14.0 for 2010 , 50.0 for 2013..... And still pay attention to 64 vs 32 bit – Matt Jul 05 '16 at 22:28
  • @Matt - 2010 and later versions of the OLEDB provider register themselves as `Microsoft.ACE.OLEDB.12.0` even though they are actually 14.0, etc.. so uninstalling/reinstalling should not be necessary. – Gord Thompson Jul 05 '16 at 23:12
  • @GordThompson my own experience with the access database engines has been contradictory to what you have stated and I have infact had to install in the order I mentioned. the wikipedia article states that 2010 (ACE 14) discontinued support for older versions of Jet(1.0-2.0) but still maintained some support for 3.0-4.0 which should include office 2007. 2013 dropped support everything except 4.0. So some backwards compatibility was maintained during the new releases and some not............... – Matt Jul 06 '16 at 00:01
  • Appreciate your help guys! I tried all of the above and none worked. To answer your questions, I am running a 32 bit system but don't know if I'm importing a 32 or 64 bit process. How do I tell? I have installed and reinstalled Access Database Engine 2010 and tried other providers. If you can think of anything else I'd be willing to give it a try. Thanks again. – Todd181 Jul 06 '16 at 12:39
  • Just wanted to let you guys know I found a solution - Use OPENDATASOURCE instead of OPENROWSET. Here is the query: Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Data Source = "C:\Users\td\Dropbox\Utilization Reports\DB\201606\Careplus\IMA_0216_v2k.mdb"' )...Backuplabels – Todd181 Jul 08 '16 at 13:32

0 Answers0