Solved

MS Access app wont close when SERVER 2008 scheduler is used.

Posted on 2010-09-01
20
350 Views
Last Modified: 2012-05-10
I have an MS Access 2007 app that I would like to open using Server 2008 task scheduler.  When opened, an autoexec macro is run and the app is automatically closed using VBA.  

When I launch the app using windows explore or open access and choose the database to open, the macro runs and the database closes.  This is good!

I then created a batch file that Calls the database, and that also runs without problems.

When I created a Scheduled Task, the database opens and runs, but does not close.   Does anyone know why this is happening.
0
Comment
Question by:momo4kids
[X]
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
  • 10
  • 4
  • 3
  • +2
20 Comments
 
LVL 9

Expert Comment

by:javajws
ID: 33583807
What VBA are you using to close the database.

"DoCmd Quit" should work.
0
 
LVL 1

Author Comment

by:momo4kids
ID: 33583815
I am using Docmd.quit.  It works when I run it outside the scheduler.
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 33583852
You can try the CloseCurrentDatabase method. It works when Access is opened via automation.

http://msdn.microsoft.com/en-us/library/bb237799(office.12).aspx
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 75
ID: 33583917
Can you post the code around the close operation ...

mx
0
 
LVL 1

Author Comment

by:momo4kids
ID: 33583924
Where would I put the CloseCurrentDatabase command?
0
 
LVL 1

Author Comment

by:momo4kids
ID: 33583954
I tried a docmd.closedatabase at the end of my code.  Still didnt work.
0
 
LVL 75
ID: 33583961
"at the end of my code.  "

Can you post this code ?

mx
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 33583974
You have to make sure that you declare a variable as an Access. application with the Dim statement and set it to nothing at the end as well.
<your sub name>
Dim appAccess As Access.Application

    ' Create new instance of Microsoft Access.
    Set appAccess = CreateObject("Access.Application")
    ' Open database in Microsoft Access window.
    appAccess.OpenCurrentDatabase strDB
    
<your code here>

    ' Close currently open database.
    appAccess.CloseCurrentDatabase
    Set AppAccess = Nothing

end sub

Open in new window

0
 
LVL 1

Author Comment

by:momo4kids
ID: 33584001
How much of it do you need?  all the code works until after the report is printed.  All of the Public sub routines are working. Here it is.

Option Compare Database

Private Sub Form_Load()

On Error GoTo Err_Form_Load
Dim dbs As DAO.Database, rst As DAO.Recordset, rst2 As DAO.Recordset, rst3 As DAO.Recordset
Dim stDocName As String, stDocName1 As String, strSQL As String, ReDir As String

DoCmd.RunCommand acCmdAppMinimize

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblImportTableList", dbOpenDynaset) 'Select list of tables to delete and link

rst.MoveLast

DoCmd.Hourglass (True)
DoCmd.SetWarnings (False)

GetClientData 'read client info file....set global var


stDocName1 = "qryAppendtblTransactions" 'get query to append transactions

'******Link Access Tables*******
DoCmd.TransferDatabase acLink, "Microsoft Access", gblstrDBPath, acTable, "tblLinkedTableList", "tblLinkedTableList"
Set rst3 = dbs.OpenRecordset("tblLinkedTableList", dbOpenDynaset) 'Select list of access tables to link
LinkAccessTables dbs, rst3, Forms![frmUploadWebData], stDocName1
'******End Delete SQL Tables*******


Me.[Text3] = "" 'reset type to null

'******Delete SQL Tables*******
DeleteSQLTables dbs, rst, Forms![frmUploadWebData], stDocName1
'******End Delete SQL Tables*******

'******Link SQL Tables*******
LinkSQLTables dbs, rst, Forms![frmUploadWebData], stDocName1
'******End Link SQL Tables*******

'*******Append SQL data to Access tables******
strSQL = "SELECT tblQueryList.QueryName FROM tblClientInfo INNER JOIN tblQueryList ON tblClientInfo.ClientID = tblQueryList.ClientID WHERE (((tblClientInfo.Current) = Yes)) ORDER BY tblQueryList.Sort;"
Set rst2 = dbs.OpenRecordset(strSQL, dbOpenDynaset) 'Select list of queries to run
rst2.MoveLast
AppendSQLData dbs, rst2, Forms![frmUploadWebData], stDocName1
'*******End Append SQL data to Access tables******

DoCmd.OpenQuery "qryAppendUploadDateandTime", acNormal, acEdit 'Append upload Date and time


'******Delete Tables*******
DeleteSQLTables dbs, rst, Forms![frmUploadWebData], stDocName1

DeleteAccessTables dbs, rst3, Forms![frmUploadWebData], stDocName1
'******End Delete SQL Tables*******

'DoCmd.OpenReport "rptUploadDetails", acPrint


DoCmd.SetWarnings (True)
DoCmd.Hourglass (False)

'DoCmd.RunCommand acCmdAppMaximize
DoCmd.CloseDatabase
DoCmd.Quit

Exit_Form_Load:
    Exit Sub

Err_Form_Load:
    MsgBox Err.Description

    DoCmd.SetWarnings (True)
    DoCmd.Hourglass (False)
    DoCmd.Quit
End Sub


0
 
LVL 75
ID: 33584022
Try the code that jmiller1979 posted ...

mx
0
 
LVL 57
ID: 33585155
I've had problems with Access and task scheduler as well, but with Server 2003.  But it takes the form of Access generating a C000005 application fault when it goes to quit.
  Not sure what it going on.  Did all kinds of troubleshooting with it and only concluded that it was the fault of Windows Scheduler and/or Server.  
  As with you, the apps worked perfectly fine on their own and only had an issue running under task scheduler.  In my case, I was able to change the debug options so it simply recorded the fault to a log with no prompting and it would still quit.
A couple of suggestions:
1. Make sure the task is only running when a user is logged in and that user is the same as the one that is setup to run the task.

2. Go through the app and make sure that all objects are closed and your setting object variables to nothing.
JimD.
0
 
LVL 1

Author Comment

by:momo4kids
ID: 33588942
I am still confused.  

Jmiller1979 suggest that I use code that opens a new instance of my access app after I already have it opened.  I tried this on the server and had over 200 instance of access open.  It was a nightmare.

JDettman suggests that I set the scheduler to run only when the user is logged in.  Even when I am logged in testing, it doesn't work.

JDettman also suggests that I be sure to close all objects and set object variables to nothing.  Would the DB close outside of the schedule task if these things were not done already.

Here's my problem again.  I scheduled a task that calls a bat file.  There are only 2 lines in the bat file

"C:\Program Files (x86)\Microsoft Office\Office12\msaccess.exe" "D:\Shares\Public\Access DBs\AgLogicUpload.accde"
Exit

The accde file opens and runs the code, but does not close access at the end of the code.  (Code shown above).  When the bat file is run outside of the scheduler, the DB opens and access closes without problems.  The problem must be with the scheduler.  I have attached print screens of what I have scheduled.
Doc1.pdf
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 33589261
<<JDettman suggests that I set the scheduler to run only when the user is logged in.  Even when I am logged in testing, it doesn't work.>>
OK.
<<JDettman also suggests that I be sure to close all objects and set object variables to nothing.  Would the DB close outside of the schedule task if these things were not done already.>>
Possibly.
<<I scheduled a task that calls a bat file.  >>
I would try calling Access directly from the task rather then a .bat file and see if that changes anything.
JimD.
0
 
LVL 1

Author Comment

by:momo4kids
ID: 33590064
Tried calling the database directly,   I have attached the Scheduler results.
Doc2.pdf
0
 
LVL 1

Author Comment

by:momo4kids
ID: 33590077
I have even created a vb.exe file to call the database and called that in the scheduler.  Database still will not close.
0
 
LVL 57
ID: 33590785
<<Database still will not close>>
I'm afraid your to the point of calling Microsoft.  There is nothing your doing wrong.  
As I mentioned earlier, I have problems with the scheduler under Server 2003.  Granted specifically it's a different problem, but it's the same general thing; apps run fine on their own or from a batch file, but when called from the scheduler it fails in some way.
I spent days troubleshooting and never got to the bottom of it.  And I have no doubts that it was the scheduler that was at fault.  Only reason I stopped working on it is because I had a work a round.
I was hoping that by changing the setup (calling Access directly), you might find a work a round as well.
Sorry :(
Only other thing I can offer is to write your own scheduler.  I have a time clock polling system which you could use as a start if you want, but it is no where near as sophisticated in scheduling as task scheduler is.
JimD.
 
0
 
LVL 1

Author Comment

by:momo4kids
ID: 33591517
Any suggestions on a free scheduler?
0
 
LVL 1

Author Comment

by:momo4kids
ID: 33593386
I ended up purchasing a simple scheduler.  I never got it to work on Windows Server 2008.
0
 
LVL 1

Author Closing Comment

by:momo4kids
ID: 33593393
My issue was never resolved, but I thought JDettman deserved some credit
0
 
LVL 57
ID: 33595582
<<I ended up purchasing a simple scheduler.  I never got it to work on Windows Server 2008.>>
 Simple scheduler didn't work either?  That's the problem I ran into with my 2003 problem.  Seemed like no matter what I did, I had an issue.
JimD.
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

732 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