opendatasource to retrieve data from Access 2007

• camino.april

  SSC Veteran

  Points: 252

  More actions

  January 12, 2010 at 1:24 am

  #137204

  Hi at all!

  I'm trying to execute this statement :

  " SELECT * into #tbltemp

  FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\CFF.accdb')...[T_prodotti] "

  It doesn't work! I have the following error:

  Msg 7308, Level 16, State 1, Line 1

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

  I don't know if is the reason of the problem, but in the SSMS Server > Server Objects > Linked Servers > Providers folder I don't have "Microsoft.ACE.OLEDB.12.0".

  Could anyone help me, please ?

  Antonio

• michael.catalano

  SSC Enthusiast

  Points: 130

  More actions

  January 27, 2010 at 10:15 am

  #1109411

  Try one of these:

  SELECT * into #tbltemp FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source="C:\CFF.accdb";')...[T_prodotti]

  or

  SELECT * into #tbltemp FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source="C:\CFF.accdb";User ID=Admin;Password=; ')...[T_prodotti]