Solved

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

Posted on 2010-09-01
20
305 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
  • 10
  • 4
  • 3
  • +2
20 Comments
 
LVL 9

Expert Comment

by:javajws
Comment Utility
What VBA are you using to close the database.

"DoCmd Quit" should work.
0
 
LVL 1

Author Comment

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

Expert Comment

by:Jerry Miller
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Can you post the code around the close operation ...

mx
0
 
LVL 1

Author Comment

by:momo4kids
Comment Utility
Where would I put the CloseCurrentDatabase command?
0
 
LVL 1

Author Comment

by:momo4kids
Comment Utility
I tried a docmd.closedatabase at the end of my code.  Still didnt work.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"at the end of my code.  "

Can you post this code ?

mx
0
 
LVL 18

Expert Comment

by:Jerry Miller
Comment Utility
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
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Try the code that jmiller1979 posted ...

mx
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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
Comment Utility
<<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
Comment Utility
Tried calling the database directly,   I have attached the Scheduler results.
Doc2.pdf
0
 
LVL 1

Author Comment

by:momo4kids
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
Comment Utility
Any suggestions on a free scheduler?
0
 
LVL 1

Author Comment

by:momo4kids
Comment Utility
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
Comment Utility
My issue was never resolved, but I thought JDettman deserved some credit
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
New Windows 7 Installations take days for Windows-Updates to show up and install. This can easily be fixed. I have finally decided to write an article because this seems to get asked several times a day lately. This Article and the Links apply to…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

10 Experts available now in Live!

Get 1:1 Help Now