how to connect to ms Access from SQL via ADO

• dchitm

  SSC Enthusiast

  Points: 174

  More actions

  December 19, 2003 at 12:00 pm

  #161007

  I have a trigger in SQL which fires a stored procedure whenever there is an update in the SQL table. The Stored procedure does the following: 1. Establish a connection to an MS Access db. via jet.OLEDB method. 2. Insert a recored to a table.

  Here is the issue that I'm having:

  Wher the trigger fires the stored proceduer, nither of them generate any error messages, but no record has been inserted to the Access table!?! However, when I run them induvidually, they seemed to be ok.

  I'm looking for another ways to do the job, and I would like to know how to do it using ADO.

  Thanks.

• K. Brian Kelley

  SSC Guru

  Points: 114532

  More actions

  December 19, 2003 at 12:08 pm

  #487277

  Have you tried establishing a "permanent" linked server connection and referring to the Access DB by the four part naming convention in that manner?

  K. Brian Kelley, GSEC

  http://www.truthsolutions.com/

  Author: Start to Finish Guide to SQL Server Performance Monitoring

  http://www.netimpress.com/

  K. Brian Kelley
  @kbriankelley