Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


run a macro within a database without opening database.

Posted on 2011-03-07
Medium Priority
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 85
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 85
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.
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

LVL 58
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 58
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 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 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

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

580 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