Automate MS Access application

Here is some code that opens an Access database and does some things, including running an action query.  I would suggest first running the code from another Access database, and if it works, then you can run the query or code from a VBScript that could be run from the Windows Scheduler.  Here is the VBA code:

Public Sub OpenAnotherDatabase()
'Created by Helen Feddema 14-Feb-2010
'Last modified by Helen Feddema 14-Feb-2010

   Dim appAccess As New Access.Application
   Dim strDBNameAndPath As String
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim dbe As DAO.DBEngine
   
   'Change to your db name and path
   strDBNameAndPath = "G:\Documents\Access 2002-2003 Databases\General.mdb"
   appAccess.Visible = True
   appAccess.OpenCurrentDatabase filepath:=strDBNameAndPath, _
      exclusive:=False
      
   'Run a procedure
   'appAccess.Run "PrintOrdersReport"
   
   'Run a macro
   'appAccess.DoCmd.RunMacro "mcrPrintOrdersReport"
   
   'Run an action query
   'appAccess.DoCmd.OpenQuery "qryDeleteSomeOrders"
   
   'Run SQL code
   strSQL = "DELETE tblOrders.ShippedDate FROM tblOrders WHERE ShippedDate = #8/4/1994#;"
   Debug.Print "SQL string: " & strSQL
   'appAccess.DoCmd.RunSQL strSQL
   
   'Iterate through a recordset
   Set dbe = appAccess.DBEngine
   Set dbs = dbe.OpenDatabase(strDBNameAndPath)
    
   Set rst = dbs.OpenRecordset("tblCategories")
   Do Until rst.EOF
      Debug.Print rst![CategoryName]
      rst.MoveNext
   Loop
   rst.Close
    
   Set dbs = Nothing
   Set appAccess = Nothing
   
End Sub

Select allOpen in new window

And here is a VBScript:

Dim appAccess  
Dim strDBName 
 
Set appAccess= WScript.CreateObject("Access.Application") 
strDBName = "D:\Documents\Northwind.mdb" 
appAccess.OpenCurrentDatabase strDBName 
appAccess.Run "SendPDFReport"

Select allOpen in new window

MS Access also offers data engine so you may use almost any language of your choice which allows ODBC, JDBC, or OLE DB data access. Candidates are C#, VB.NET, Java, FoxPro, etc.  Simply define the connection string to the Access MSDB or ACCDB file and post/execute the query.

Hi donnie91910,

was our answers sufficient for you? Did you create/try the code for Access query?

BTW, what is the purpose of the query? Or better said what do you plan to do with query results? If we are talking about some UPDATE query then it should be OK but if your query just selects some data from Access tables then it would require some additional processing.

TIA
Pavel