Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

run a macro within a database without opening database.

Posted on 2011-03-07
Last Modified: 2012-06-27
This is in relation to post http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26862671.html

Currently I'm leaving an active signon running on my server.  A task opens my secure databases at a scheduled time each day or week and runs a macro that calls a VBA function, which sends out status notifications via outlook.  

Is it possible to run this macro/code without an active signon on my server.  I'd like to remain logged off without access running in the background until the task starts.  If not possible in access, is this possible in SQL? thanks
Question by:valmatic
  • 4
  • 3
  • 2
LVL 84
ID: 35058336
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.

Author Comment

ID: 35058460
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.
LVL 84
ID: 35058602
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.
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

LVL 57
ID: 35058658

  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.


Author Comment

ID: 35059774
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

Open in new window

LVL 57
ID: 35059908
<<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.


Author Comment

ID: 35062681
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]"
    '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])
                    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])
                    End With
                End If
            End If

 'cut out more email sends based on various parameters.

Open in new window

LVL 57

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 35069076
<< I have Advanced security for outlook running and in 2010, without outlook running advanced security gets hung up until I open outlook fully.  >>

  That's an issue with CDO and/or using any e-mail client.  If all your doing is sending e-mails, I would go with the simplest route possible, which is talking directly to a SMTP server to send the mail.

  You can do that with BLAT or vbSendMail, both of which are free.

vbSendMail:  http://www.freevbcode.com/ShowCode.Asp?ID=109
BLAT:  www.blat.net

  If you do want to stick with CDO and Outlook, then you can use something like redemtion to by-pass the security:



Author Closing Comment

ID: 35138659
Jim,  Thanks for the info

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question