To Run An MS Access Macro From SQL

• deek

  SSCommitted

  Points: 1886

  More actions

  March 19, 2003 at 4:50 pm

  #105633

  Does anybody know how to call a Macro in an MS Access database from SQL?

• philcart

  SSC-Forever

  Points: 47794

  More actions

  March 21, 2003 at 12:07 am

  #451661

  Don't know if this helps any, but maybe you could execute it by creating a Access object and referring to the macro via that object.

  Here's and example of creating an Excel object. It could be the same sort of thing for Access?!?

  
  
  Option Explicit
  
  
  
  Function Main()
  
  
  
  Dim oXLS 
  
  Dim oWbk  
  
  Dim sWsht 
  
  Dim iCount
  
  Dim bFound
  
  
  
  Dim sFilename
  
  Dim sWorkSheet
  
  
  
  sFilename = DTSGlobalVariables("sFilename").Value
  
  sWorkSheet = DTSGlobalVariables("sWorkSheet").Value
  
  
  
  Set oXLS = CreateObject("Excel.Application")
  
  
  
  ' Open the workbook specified
  
  Set oWbk = Excel_Application.Workbooks.Open(sFilename)
  
  
  
  ' get a count of worksheets in the workbook
  
  iCount = oWbk.WorkSheets.Count
  
  
  
  ' if there is more than one worksheet
  
  If iCount > 1 then
  
  ' loop through worksheets collection
  
  For Each sWsht in oWbk.WorkSheets
  
  ' Find the WorkSheet specified
  
  If oWsht.Name = CStr(sWorkSheet) Then
  
  ' delete the worksheet
  
  oWbk.Worksheets(sWorkSheet).Delete
  
  ' save the workbook
  
  oWbk.Save
  
  End if
  
  Next
  
  End if
  
  
  
  ' Remove objects created
  
  Set sWsht = Nothing
  
  oWbk.Close
  
  Set oWbk = Nothing
  
  oXLS.Quit
  
  Set oXLS = Nothing
  
  
  
  Main = DTSTaskExecResult_Success
  
  
  
  End Function
  
  

  Hope this helps

  Phill Carter

  --------------------

  Colt 45 - the original point and click interface

  --------------------
  Colt 45 - the original point and click interface