Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

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

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
momo4kids
Asked:
momo4kids
  • 10
  • 4
  • 3
  • +2
1 Solution
 
javajwsCommented:
What VBA are you using to close the database.

"DoCmd Quit" should work.
0
 
momo4kidsAuthor Commented:
I am using Docmd.quit.  It works when I run it outside the scheduler.
0
 
Jerry MillerCommented:
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
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Can you post the code around the close operation ...

mx
0
 
momo4kidsAuthor Commented:
Where would I put the CloseCurrentDatabase command?
0
 
momo4kidsAuthor Commented:
I tried a docmd.closedatabase at the end of my code.  Still didnt work.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"at the end of my code.  "

Can you post this code ?

mx
0
 
Jerry MillerCommented:
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
 
momo4kidsAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Try the code that jmiller1979 posted ...

mx
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
momo4kidsAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
momo4kidsAuthor Commented:
Tried calling the database directly,   I have attached the Scheduler results.
Doc2.pdf
0
 
momo4kidsAuthor Commented:
I have even created a vb.exe file to call the database and called that in the scheduler.  Database still will not close.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
momo4kidsAuthor Commented:
Any suggestions on a free scheduler?
0
 
momo4kidsAuthor Commented:
I ended up purchasing a simple scheduler.  I never got it to work on Windows Server 2008.
0
 
momo4kidsAuthor Commented:
My issue was never resolved, but I thought JDettman deserved some credit
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

  • 10
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now