Openrowset with Access 2010

• crowegreg

  SSC Eights!

  Points: 965

  More actions

  May 19, 2015 at 1:35 pm

  #298500

  Hello, I routinely need to import data into SQL DB from Access 2010.I've tried using both Openrowset and Opendatasource, and I'm receiving similar errors. I've tried reaching out to my corporate network support, but no assistance has been provided.

  Here's my code I'm trying:

  SELECT *

  FROM OpenRowset('Microsoft.Jet.OLEDB.4.0','C:\ITS Database\CHI_ITS_v2013a.accdb';'admin';'',Sheet2)

  Here's the error message I'm receiving:

  Msg 7308, Level 16, State 1, Line 1

  OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

  Thanks in advance!!

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 19, 2015 at 1:58 pm

  #1799148

  well first, it's the wrong driver:'Microsoft.Jet.OLEDB.4.0' is for 32 bit, and is only good for Access 2003 and below;

  anything above requires the new ACE driver, which is available in 32 and 64 bit.

  from there, i've never successfully got a linked server to work for access via the Ace drivers unless i created a DSN on the server.

  it's been a while, but here are my notes i wrote previously:

  I've found the hard wat there are multiple prerequisites to get Access to work via a Linked server.

  install the AccessDatabaseEngine_x64.exe from microsoft:

  http://www.microsoft.com/en-us/download/details.aspx?id=13255

  make sure you open an Administrative command prompt window, and run it with the c:\Downloads\AccessDatabaseEngine_x64.exe /passive

  parameter / command line flag;

  this will force the install of the drivers, even if you have 32 bit office installed;

  otherwise you get some error about 32 bit Office preventing the install.

  After that is installed:

  --Required settings for the provider to work correctly as a linked server

  EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

  GO

  EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

  After that, you must create a DSN for it:

  I've never gotten a DSN-less connection to work.

  Start>>Administrative Tools>>Data Sources (ODBC)

  Eitehr select the default existing, or create a new one: you'll need the name of this later for your linked server.

  Here', I'm using the default "MS Access Database"

  

  Click The Configure button, and on this screen, click Select (or create for a blank database)

  and find the database you want to connect to:

  

  Click all the Saves until you escape the Data Sources (ODBC) Program.

  Now finally, go to your SQL server, and use the code below to add your linked server:

  --#################################################################################################

  --Linked server Syntax for Access with ACE 64 driver

  --#################################################################################################

  EXEC sp_addlinkedserver

  @server = N'MyAccessDB1',

  @srvproduct=N'Access',

  @provider=N'MSDASQL',

  @datasrc=N'MS Access Database', --Name of DSN from odbc 64 administrator

  @provstr=N'MSDASQL',

  @catalog=N'c:\data\Music.accdb'; --pathname to accdb file

  EXEC sp_addlinkedsrvlogin

  @rmtsrvname = N'MyAccessDB1',

  @useself = N'TRUE',

  @locallogin = NULL,

  @rmtuser = N'Admin',

  @rmtpassword = NULL;

  if everything is set up correctly, this will return results:

  EXEC sp_tables_ex MyAccessDB1;

  .

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!