Solved

run a macro within a database without opening database.

Posted on 2011-03-07
9
468 Views
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
0
Comment
Question by:valmatic
  • 4
  • 3
  • 2
9 Comments
 
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.
0
 
LVL 7

Author Comment

by:valmatic
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.
0
 
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.
0
 
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.

JimD.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 7

Author Comment

by:valmatic
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

0
 
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.

JimD.
0
 
LVL 7

Author Comment

by:valmatic
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]"
    
    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.

Open in new window

0
 
LVL 57

Accepted Solution

by:
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:

http://www.dimastr.com/redemption/

JimD.
0
 
LVL 7

Author Closing Comment

by:valmatic
ID: 35138659
Jim,  Thanks for the info
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

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…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now