run a macro within a database without opening database.

Do you mean an active signon to your Server? If so, in most cases you can set a Scheduled Task to use a specific login account, assuming you have the credentials for that account.

If you're referring to something else please clarify.

Hi LSM,  I've set the task to use a specific logon and have set the task to run even if the server is logged off.  My problem is that (correct me if I'm wrong) access needs to be open to run the macro and Outlook needs to be open and logged in to send the emails.  I think I have a workaround for the email portion but am I wrong in thinking a secured access database has to be open to run this macro?  I do have the code written to open the database, log in and run the macro, as part of the script but since windows is logged off, access can't open.  I guess I'm wondering if there is some way to run it in the background or maybe use soem sort of script to log into windows so everything can fire off correctly?  Hope that makes sense.

Yes, Access must be open to run your macro. Your Task should "log into" Windows if needed, but I'm not sure if that's what we're referring to or not.

What does your macro do? If all it does is grab data, you can use another environment (like VB6 or VB.NET) to connect to the database and get the data.

  There are a number of issues in this area.  Depending on the OS and the app, I've found everything from:

1. You never can get it to run without being logged on.

2. You can get it to run if you don't interact with the desktop in any way.

3. You can get it to run if you schedule it with the NT Authority account.

4. You can get it to run if you set the task schedulers "Interact with desktop" property in services.

JimD.

How would I add into the task a logon for windows.  If this is the case I would need the task to log into windows to start a session, start outlook, open my access database run the macro to send the emails and then close the database, close outlook and finally log off the windows session.  Here's my task as it stands now.

"C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE" "s:\Quality Management System\_Records\Record Database\CAPADB\capalog.mdb" /user Auser /pwd password /WRKGRP "s:\Quality Management System\_Records\Record Database\CAPADB\secured.mdw" /x LateCAPANotice

Select all Open in new window

<<How would I add into the task a logon for windows. >>

  In task scheduler, you right click on a task, then go to properties and the "Run as" box should be on the first tab.

<< If this is the case I would need the task to log into windows to start a session, start outlook, open my access database run the macro to send the emails and then close the database, close outlook and finally log off the windows session.  Here's my task as it stands now. >>

  But I don't think that's what your asking.  Your task cannot logon/off Windows in the sense your thinking.

  Your task as it stands would entered into task scheduler as a task.  It would then "Run as" some user when it executes.

  Your macro then would need to carry out all the tasks that needed to be accomplished, like sending mail.  Not sure why or what you'd be opening Outlook for directly.  Normally you'd do that through OLE automation.

JimD.

I was hoping there would be a simpler solution lol - sorry all.  Anyway....

macro calls my code that sends both net sends and emails based on some parms within my database.  I'm finding that I have to have Outlook open though to ensure the CDO script runs.  I have Advanced security for outlook running and in 2010, without outlook running advanced security gets hung up until I open outlook fully.  If I leave Outlook open then it runs through fine.  I attached the function my macro is calling.  Shortened it up a little but this is basically it.  create the outlook object and send it along.  Is there a different way I should be programming this?  I'm pretty much self taught in VBA so I'm open to whatever you have to say.  Maybe we're getting off topic too so not sure how far we want to go with it.  

I do hear what you're saying about the windows login - just wondering if that was possible?

Option Compare Database
'Send pop-up message or email notice based on timeliness of a given CAPA.  At a specified time,
'this code scans the capalog table via the SUP_CAPANotice query and notifies necessary parties.
'The time is set by a Scheduled task on BobI's PC.  If Bob's PC is off then the notification does not run.

Public Function Notify()
On Error GoTo Err_Notify

    Dim stAppName As String
    Dim winuser As String
    Dim capano As String
    Dim Ass As String
    Dim supID As String
    Dim sec3due As String
    Dim sec4due As String
    Dim dept As String
    Dim SS1 As String
    Dim SS2 As String
    Dim Descr As String
    Dim rst01 As ADODB.Connection
    Set rst01 = CurrentProject.Connection
    Dim myRecordSet As New ADODB.Recordset
    myRecordSet.ActiveConnection = rst01
        
    myRecordSet.Open "[SUP_CAPANotice]"
    
    myRecordSet.MoveFirst
    
    '----------------------------------------
    'Issue Notices for Section III late Capas'
    'send pop-ups to assignee and email to both assignee and supervisor
          
        Do Until myRecordSet.EOF
        
            If IsNull(myRecordSet.Fields(12).Value) Then                '(If sec 3 complete date is blank)
                If myRecordSet.Fields(11).Value < Now() Then            '(If sec 3 due date is < today)
                
                    winuser = myRecordSet.Fields(29).Value
                    capano = myRecordSet.Fields(0).Value
                    Ass = myRecordSet.Fields(8).Value
                    supID = myRecordSet.Fields(31).Value
                    sec3due = myRecordSet.Fields(11).Value
                    Descr = myRecordSet.Fields(18).Value
                    
                    'SEND POPUP to Assignee
                    stAppName = "net send " + [winuser] + " CAPA NO. " + [capano] + ", assigned to you, was Due: " + [sec3due] + " and is Late."
                    Call Shell(stAppName, 1)
                    'stAppName = "net send " + [supID] + " SUPERVISORY ALERT - CAPA NO. " + [capano] + ", assigned to " + [Ass] + ", was Due: " + [sec3due] + " and is Late.  " + _
                    '"Please review this CAPA with your employee as necessary.  Thank you..."
                    'Call Shell(stAppName, 1)
                    
                    'ALSO SEND EMAIL to Assignee
                    Dim Outlook
                    Set Outlook = CreateObject("Outlook.Application")
    
                    Dim Message 'As Outlook.MailItem
                    Set Message = Outlook.CreateItem(olMailItem)
                    With Message
    
                     .Subject = "CAPA ALERT - Your CAPA is Late"
                     .Body = " CAPA ALERT - CAPA No. " + [capano] + ", assigned to you, is late as of " + [sec3due] + _
                     ".  Description: " + [Descr] + ". Notice has also been sent to your immediate supervisor.  Thank you..."
                     .Recipients.Add ([Ass])
    
                     .Send
                    End With
                    
                    'AND EMAIL to SUPERVISOR
                    Set Outlook = CreateObject("Outlook.Application")
                    Set Message = Outlook.CreateItem(olMailItem)
                    With Message
    
                     .Subject = "SUPERVISORY CAPA ALERT - A CAPA for your department is Late"
                     .Body = " CAPA ALERT - CAPA No. " + [capano] + ", assigned to " + [Ass] + ", is late as of " + [sec3due] + _
                     ".  Description: " + [Descr] + ". Please verify that your employee is working on this CAPA.  " + [Ass] + " has also been notified.  Thank you..."
                     .Recipients.Add ([supID])
    
                     .Send
                    End With
                    
                End If
            End If
                    
        myRecordSet.MoveNext
        
        Loop

 'cut out more email sends based on various parameters.

Select all Open in new window

Jim,  Thanks for the info