How can I close access in vba code?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

luis_a_roman - 23 May 2008 14:08 GMT

I have a macro that is schedule to run every day to copy a database. However,
when I execute the macro it does not allow me to close access. Access is open
to execute the macro but would like to close it automatically (as part of
this macro). I added a QUIT macro but give me an error. Does any can help me
to complete this macro and be able to close Access. The code is below.

Thank you for guidance and collaboration.

Luis

Function CopyITPlanningDatabase()

' Using the Scripting.FileSystemObject to copy files
' Requires a reference to Microsoft Scripting Runtime Library
' In any code window Menu > Tools > References
' Microsoft Scripting Runtime Library - Checked

Dim strSourceFile As String
Dim strDestinationPath As String

Dim objFso As Scripting.FileSystemObject

' Set the target file path
strSourceFile = "\\FTWSS02\BIGBUCKS\itplan-copy\itplan.mdb"

' Set the target file path
strDestinationPath = "\\ftwgroups\jsf\jsfitcoord\acWKspace\F-35 Dash Board\
"

' Instantiate instance of Scripting file system object
Set objFso = New Scripting.FileSystemObject

' Check that the source file exists
If objFso.FileExists(strSourceFile) Then

' Check that the destination folder exists.
If objFso.FolderExists(Mid(strDestinationPath, 1, InStrRev
(strDestinationPath, "\", , vbTextCompare))) Then

' Copy the file, overwriting existing file of same name...
strDestinationPath = strDestinationPath & "LRitplan.mdb"
objFso.CopyFile strSourceFile, strDestinationPath, True
Else
' maybe use objFso.Buildpath to create the folder
' depends how bulletproof you want to make this.

End If

End If
DoEvents
' Destroy instantiated objects
Set objFso = Nothing

End Function

Reply to this Message

Klatuu - 23 May 2008 14:39 GMT

You don't provide nearly enough information for anyone to help.
What error are you getting?
Where does the error occur?
You posted the code for a function, but don't say where it is called from.
when does the Quit marco execute?
Why not just include Docmd.Quit as the last action in the fuction?
Or, is there something else you run you did not mention?

Signature

Dave Hargis, Microsoft Access MVP

> I have a macro that is schedule to run every day to copy a database. However,
> when I execute the macro it does not allow me to close access. Access is open
[quoted text clipped - 50 lines]
>
> End Function

Reply to this Message

luis_a_roman - 23 May 2008 15:18 GMT

Your are correct the error that I'm getting after adding the instruction
below is;

Macro action cancelled. "You used a method of the DoCmd object to carry out
an action in Visual Basic, but then clicked Cancel in a dialog box. For
example, you used the Close method to close a changed form, then clicked
Cancel in the dialog box that asks if you want to save the changes?

Also, want to let you know that I have added the following code;
Set objFso = Nothing
docmd.quit
End Function

The error occured after the completion of the code at closing time. The macro
is schedule to execute every day at 8:00 pm (CST).

I added docmd.quit as you suggested but still getting the error mentioned
above. Also, this macro is executed in MS Access 2007.

Appreciate your feedback and time helping me find the root cause of this
problem.

>You don't provide nearly enough information for anyone to help.
>What error are you getting?
[quoted text clipped - 8 lines]
>>
>> End Function

Reply to this Message

Klatuu - 23 May 2008 15:45 GMT

using the FileSystemObject is really not necessary in this case. It is only
adding additional overhead. I don't know if it is causing the problem. I
can't really see what the problem might be, but just as a test, try this
version:

The only other thing I can think of is that probably you are running the
function from an autoexec macro. I don't normally use macros, so I am not
that familiar with their behaviour. I know you have to use the RunCode
action to run the query, but maybe rather than include the Docmd.Quit in the
function, you may need another line in the macro to do the quit.

Function CopyITPlanningDatabase()
Dim strSourceFile As String
Dim strDestinationPath As String

On Error GoTo CopyIT_Exit

' Set the target file path
strSourceFile = "\\FTWSS02\BIGBUCKS\itplan-copy\itplan.mdb"

' Set the target file path
strDestinationPath = "\\ftwgroups\jsf\jsfitcoord\acWKspace\F-35 Dash
Board\
"

' Check that the source file exists
If Dir(strSourceFile) = vbNullString Then 'The Source File does not
exist
Goto CopyIT_Exit
End If

' Check that the destination folder exists.
If Dir(strDestinationPath) = vbNullString Then
MkDir(strDestinationPath)
End If

' Delete the Destination file if it already exists
strDestinationPath = strDestinationPath & "LRitplan.mdb"
If Dir(strDestinationPath) <> vbNullString Then
Kill strDestinationPath
End If

'Copy the file
Filecopy strSourceFile, strDestinationPath

CopyIT_Exit:
Docmd.Quit

End Function

Signature

Dave Hargis, Microsoft Access MVP

> Your are correct the error that I'm getting after adding the instruction
> below is;
[quoted text clipped - 30 lines]
> >>
> >> End Function

Reply to this Message

luis_a_roman - 23 May 2008 16:12 GMT

Really appreciate your collaboration. However, I execute the revised code and
still getting the same problem. Don't understand because when I execute the
macro manually it works correctly. Wonder if is the MS scheduler.

Thank you, Luis

>using the FileSystemObject is really not necessary in this case. It is only
>adding additional overhead. I don't know if it is causing the problem. I
[quoted text clipped - 51 lines]
>> >>
>> >> End Function

Reply to this Message

Klatuu - 23 May 2008 17:30 GMT

Wouldn't be the scheduler. All it does is start the app.
Try starting the app from your desktop without the scheduler and see what
happens.

It could also be a timing issue. That is, the file hasn't finished copying
and the app wants to close. Shouldn't be, but you never know.

You might try copying the code from this site:

http://www.mvps.org/access/api/api0021.htm

and put the call to it between the copy and the quit.

See if that helps.

Signature

Dave Hargis, Microsoft Access MVP

> using the FileSystemObject is really not necessary in this case. It is only
> adding additional overhead. I don't know if it is causing the problem. I
[quoted text clipped - 80 lines]
> > >>
> > >> End Function

Reply to this Message

luis_a_roman - 23 May 2008 19:15 GMT

Really appreciate your help and patience.

I paste the code as recommended but I'm getting an error when compile.
The error is: " Only comments may appear after End Sub, End Function or End
Property".

Hate to ask but any other idea.

>Wouldn't be the scheduler. All it does is start the app.
>Try starting the app from your desktop without the scheduler and see what
[quoted text clipped - 15 lines]
>> > >>
>> > >> End Function

Reply to this Message

Klatuu - 23 May 2008 19:29 GMT

Post the code as you have it now.
What you are getting is a simple syntax error.

Signature

Dave Hargis, Microsoft Access MVP

> Really appreciate your help and patience.
>
[quoted text clipped - 23 lines]
> >> > >>
> >> > >> End Function

Reply to this Message

luis_a_roman - 23 May 2008 19:44 GMT

The code is below.

Function xCopyITPlanningDatabase()
Dim strSourceFile As String
Dim strDestinationPath As String

On Error GoTo CopyIT_Exit

' Set the target file path
strSourceFile = "\\FTWSS02\BIGBUCKS\itplan-copy\itplan.mdb"

' Set the target file path
strDestinationPath = "\\ftwgroups\jsf\jsfitcoord\acWKspace\F-35 Dash Board\
"

' Check that the source file exists
If Dir(strSourceFile) = vbNullString Then 'The Source File does not
exist
GoTo CopyIT_Exit
End If

' Check that the destination folder exists.
If Dir(strDestinationPath) = vbNullString Then
MkDir (strDestinationPath)
End If

' Delete the Destination file if it already exists
strDestinationPath = strDestinationPath & "LRitplan.mdb"
If Dir(strDestinationPath) <> vbNullString Then
Kill strDestinationPath
End If

'Copy the file
FileCopy strSourceFile, strDestinationPath

CopyIT_Exit:
' DoCmd.Quit
Application.Quit
End Function

'***************** Code Start *******************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Declare Sub sapiSleep Lib "kernel32" _
Alias "Sleep" _
(ByVal dwMilliseconds As Long)

Sub sSleep(lngMilliSec As Long)
If lngMilliSec > 0 Then
Call sapiSleep(lngMilliSec)
End If
End Sub

Sub sTestSleep()
Const cTIME = 1000 'in MilliSeconds
Call sSleep(cTIME)
MsgBox "Before this Msgbox, I was asleep for " _
& cTIME & " Milliseconds."
End Sub
'***************** Code End *********************

>Post the code as you have it now.
>What you are getting is a simple syntax error.
[quoted text clipped - 3 lines]
>> >> > >>
>> >> > >> End Function

Reply to this Message

Klatuu - 23 May 2008 19:54 GMT

This code:
'***************** Code Start *******************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Declare Sub sapiSleep Lib "kernel32" _
Alias "Sleep" _
(ByVal dwMilliseconds As Long)

Sub sSleep(lngMilliSec As Long)
If lngMilliSec > 0 Then
Call sapiSleep(lngMilliSec)
End If
End Sub

Sub sTestSleep()
Const cTIME = 1000 'in MilliSeconds
Call sSleep(cTIME)
MsgBox "Before this Msgbox, I was asleep for " _
& cTIME & " Milliseconds."
End Sub
'***************** Code End *********************
Should not be where it is. It should be in a standard module by itself.
Delete the comment lines. Do not name the module the same as the sub. Mine
is named modSleep.

Signature

Dave Hargis, Microsoft Access MVP

> The code is below.
>
[quoted text clipped - 72 lines]
> >> >> > >>
> >> >> > >> End Function

Reply to this Message

luis_a_roman - 23 May 2008 20:38 GMT

Tried without the scheduler and it works like a champ. Now I will try to
executed with the scheduler.

>This code:
>'***************** Code Start *******************
[quoted text clipped - 32 lines]
>> >> >> > >>
>> >> >> > >> End Function

Reply to this Message

luis_a_roman - 23 May 2008 20:47 GMT

The code is below. Don't understand why it works when run manually and with
the scheduler it does not. Maybe there is no solution.

'Copy the file
FileCopy strSourceFile, strDestinationPath

CopyIT_Exit:
Const cTIME = 1000 'in MilliSeconds
Call sSleep(cTIME)
DoCmd.Quit
end sub

Thank you for all your help.

Luis

>Tried without the scheduler and it works like a champ. Now I will try to
>executed with the scheduler.
[quoted text clipped - 4 lines]
>>> >> >> > >>
>>> >> >> > >> End Function

Reply to this Message

Andy - 24 May 2008 13:34 GMT

I have run into cases where the DoCmd.quit does not actually quit, but
Application.Quit does.

It does no harm to have both quit commands in the macro.

hth,
Andy

> The code is below. Don't understand why it works when run manually and with
> the scheduler it does not. Maybe there is no solution.
[quoted text clipped - 19 lines]
>> [quoted text clipped - 32 lines]
>>>>>>>>>>> End Function

Reply to this Message