VBA - How can I use the Shell()

[Guest]

HERE IS MY CODE AND MY PROBLEMS ARE DESCRIBED WITHIN IN CAPS
WITH #1- #2- #3- PRECEDING THE QUESTIONS
I WOULD REALLY APPRECIATE SOME GUIDANCE.
THANK YOU VERY MUCH..

--------------------------------------
Private Sub DuplicateDB_Click()

On Error GoTo Err_DuplicateDB_Click
Dim dbs As String
Dim lDbFileName As String
Dim appAccess As Object

#1-NOT SURE IF I'VE USE THIS CORRECTLY, I DON'T SEE ANY HOURGLASS
DoCmd.Hourglass True

lDbFileName = "D:\Dev\DB1.ldb"
dbs = "D:\Dev\DB1.mdb" 'Local Testing Location

If Len(Dir(lDbFileName)) > 0 Then
On Error Resume Next
Kill lDbFileName
If Err.Number = 0 Then 'delete was successful, file not in use
Call RefreshData 'CALL THIS PROCEDURE TO repopulate tables
Call CompactDb 'CALL THIS PROCEDURE TO COMPACT THE NEWLY
UPDATED ADHOC DB
End If
On Error GoTo Err_DuplicateDB_Click
Else
Call RefreshData 'CALL THIS PROCEDURE TO repopulate tables
Call CompactDb 'CALL THIS PROCEDURE TO COMPACT THE NEWLY
UPDATED ADHOC DB
End If

Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase dbs
appAccess.Visible = True

#2-TO THIS POINT THINGS SEEM TO BE WORKING BUT AS SOON AS THE EXIT SUB
COMPLETES THE DB CLOSES
AS I WAS WRITING THIS I REALIZED MAYBE I COULD USE SOMETHING LIKE
APPACTIVATE, IF THAT IS THE ANSWER
PLEASE SHOW ME HOW, IF NOT PLEASE TELL ME WHAT IS...

Exit_DuplicateDB_Click:
Exit Sub

Err_DuplicateDB_Click:
MsgBox Err.Description
Resume Exit_DuplicateDB_Click
End Sub
--------------------------------------
Private Sub RefreshData()
Dim db As DAO.Database
Set db = OpenDatabase("D:\Data\DBA\Dev\Conv
IrmaB2003\AdhocDB\IrmaB_AdhocReporting.mdb", True)
On Error GoTo Err_RefreshData
'THIS MODULE WILL RUN ALL THE DELETE AND APPEND QUERIES THAT ARE USED TO
REFRESH THE TABLE DATA

db.Execute "qd_ACH Detail", DAO.dbFailOnError
15 OTHER LINES LIKE THAT
db.Execute "qa_L_ACH Detail", DAO.dbFailOnError
SAME HERE
db.Close

Exit_RefreshData:
Exit Sub

Err_RefreshData:
MsgBox Err.Description
Resume Exit_RefreshData
End Sub

--------------------------------------
Sub CompactDb()

On Error GoTo Err_CompactDb
' This statement creates a compact version of the database
DBEngine.CompactDatabase "\\UNCPATH\db1.mdb", "\\UNCPATH\tmp.mdb"

'***** Found that using the Declaration procedure caused a *****
'***** locking issue when the compact command was issued *****
'Dim Olddb As String
'Dim Newdb As String
' Olddb = "D:\Dev\DB1.mdb"
' Newdb = "D:\Dev\tmp.mdb"
' DBEngine.CompactDatabase Olddb, Newdb

'Now lets take a few seconds to let everything settle, PAUSE.
PauseTime = 5 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer

'DELETE THE ORIGINAL DB AND RENAME THE COMPACTED DB TO THE ORIGINAL NAME
'The BAT file called here will first test if there is a backup copy of the
previously
'Compacted db, then rename the reporting/adhoc db as a backup copy, then
rename the newly
'Compacted db to the reporting/adhoc db name.

#3-I think need to use something like this
AppActivate shell("D:\Dev\RenComp.bat", 1)
in order for the module to wait until the bat file completes before
returning to the
calling mod to open the DB, DO I HAVE THE CORRECT SYNTAX ABOVE, AND IS THIS
THE PROPER USAGE??
this is what I currently have in place
Shell "D:\Dev\RenComp.bat", 1

Exit_CompactDb:
Exit Sub

Err_CompactDb:
MsgBox Err.Description
Resume Exit_CompactDb

End Sub

 

[Douglas J. Steele]

Answers in-line.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

HERE IS MY CODE AND MY PROBLEMS ARE DESCRIBED WITHIN IN CAPS
WITH #1- #2- #3- PRECEDING THE QUESTIONS
I WOULD REALLY APPRECIATE SOME GUIDANCE.
THANK YOU VERY MUCH..

--------------------------------------
Private Sub DuplicateDB_Click()

On Error GoTo Err_DuplicateDB_Click
Dim dbs As String
Dim lDbFileName As String
Dim appAccess As Object

#1-NOT SURE IF I'VE USE THIS CORRECTLY, I DON'T SEE ANY HOURGLASS
DoCmd.Hourglass True

Yes, you're using it correctly. However, you'll only see the hourglass while
Access has focus. If you move focus to some other application, you'll lose
the hourglass.

Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase dbs
appAccess.Visible = True

#2-TO THIS POINT THINGS SEEM TO BE WORKING BUT AS SOON AS THE EXIT SUB
COMPLETES THE DB CLOSES
AS I WAS WRITING THIS I REALIZED MAYBE I COULD USE SOMETHING LIKE
APPACTIVATE, IF THAT IS THE ANSWER
PLEASE SHOW ME HOW, IF NOT PLEASE TELL ME WHAT IS...

The reason your database closes is because appAccess has been declared as a
local variable in the routine, so as soon as the routine ends, it goes out
of scope. If you want the database to stay open, appAccess needs to be
declared at the beginning of the module, before any subs or functions. If
you want appAccess to be accessible from outside of the form, you're better
off declaring it in a stand-alone module (not a class module), not one
associated with a form (or report)

--------------------------------------
Sub CompactDb()

On Error GoTo Err_CompactDb
' This statement creates a compact version of the database
DBEngine.CompactDatabase "\\UNCPATH\db1.mdb", "\\UNCPATH\tmp.mdb"

'***** Found that using the Declaration procedure caused a *****
'***** locking issue when the compact command was issued *****

There's no reason for that!

'Dim Olddb As String
'Dim Newdb As String
' Olddb = "D:\Dev\DB1.mdb"
' Newdb = "D:\Dev\tmp.mdb"
' DBEngine.CompactDatabase Olddb, Newdb

'Now lets take a few seconds to let everything settle, PAUSE.
PauseTime = 5 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer

'DELETE THE ORIGINAL DB AND RENAME THE COMPACTED DB TO THE ORIGINAL NAME
'The BAT file called here will first test if there is a backup copy of the
previously
'Compacted db, then rename the reporting/adhoc db as a backup copy, then
rename the newly
'Compacted db to the reporting/adhoc db name.

#3-I think need to use something like this
AppActivate shell("D:\Dev\RenComp.bat", 1)
in order for the module to wait until the bat file completes before
returning to the
calling mod to open the DB, DO I HAVE THE CORRECT SYNTAX ABOVE, AND IS
THIS
THE PROPER USAGE??
this is what I currently have in place
Shell "D:\Dev\RenComp.bat", 1

To have Access wait until the batch file is complete, see
http://www.mvps.org/access/api/api0004.htm at "The Access Web". However, I
don't understand the point of the batch file. If all it's doing it renaming
and/or copying files, you can do that in VBA. To see whether a file exists,
use the Dir function (Len(Dir("fullpathtofile")) will be 0 if the file
doesn't exist, or non-zero if it does). To rename a file, see the Name
statement. See, too, the FileCopy and Move statements.

 

[Guest]

Thanks Doug, that all helped a great deal. I obviously have no idea as to
what the
Differences are between form modules, class modules and stand-alone modules.
Can you Explain or direct me to where I can read about the differences. I
do have the COMPACT Sub in a stand-alone module, I will move DUPLICATEDB to
that mod.

I am uncertain as to how to use SUB, Public SUB, and Private SUB, could you
please
Enlighten me?

I believe you mean for me to move the declaration of the appAccess to the
header of that Stand-alone Mod. Header seams like the right term to use here
but I am really not sure.

In line you say “There's no reason for that!†do you mean there’s no reason
for the locking Error or there’s no reason for declaring the strings?

I just opened this and will go read the references you suggested in regards
to using VB To rename and delete the files rather then shelling out to a bat
file.
IF you have the time, and would like to provide an example of how that could
be done That would be great.
Thanks again very much I appreciate all your time and help…
==========================

 

[Douglas J. Steele]

Thanks Doug, that all helped a great deal. I obviously have no idea as to
what the
Differences are between form modules, class modules and stand-alone
modules.
Can you Explain or direct me to where I can read about the differences. I
do have the COMPACT Sub in a stand-alone module, I will move DUPLICATEDB
to
that mod.

See http://msdn2.microsoft.com/en-us/library/aa139986(office.10).aspx (don't
worry that it says it's for Access 2000: it applies to all versions)

I am uncertain as to how to use SUB, Public SUB, and Private SUB, could
you
please
Enlighten me?

A Private sub is only accessible to other routines contained in that same
module. Public subs are accessible by routines anywhere.

I believe you mean for me to move the declaration of the appAccess to the
header of that Stand-alone Mod. Header seams like the right term to use
here
but I am really not sure.

Whether you move the declaration to a stand-alone module, or simply to the
top of the form module depends on what scope you need it to have. Do you
want the second instance of Access to be open until you explicitly close it
even if the form which instantiates it gets close? If so, you need to put it
in a stand-alone module. If, on the other hand, you only need that second
instance of Access while the form's open, putting it at the top of the
form's module is sufficient.

In line you say "There's no reason for that!" do you mean there's no
reason
for the locking Error or there's no reason for declaring the strings?

There's no reason that there should be a locking error.

I just opened this and will go read the references you suggested in
regards
to using VB To rename and delete the files rather then shelling out to a
bat
file.
IF you have the time, and would like to provide an example of how that
could
be done That would be great.
Thanks again very much I appreciate all your time and help.

Since I don't know exactly what your bat file does, it's difficult for me to
show how you'd do the equivalent using VBA.

 

[Guest]

SO SORRY, I am such a moron.. as I went back to the code to make changes I
realized the appAccess is declared in the ONCLICK SUB, when I made my
comments in the last post in regards to moving that sub to a stand-alone
module, I did not realize it was on ONCLICK module.
IF I call a stand-alone module from the forms module will that create any
problems running the 2nd ACCESS database when focus returns to the calling
module IF SO Can you tell me how I would run a stand-alone module from an
onclick sub in a forms module, and not have it return to the calling module??

 

[Guest]

Thank you again Doug, Yes that all make total sense. I will need to have the
second instance of access available for as long as they want, and not
dependant upon any part of the first instance. So I will need to move that
part to a stand-alon module. When I call out to that module do I need to
call ou tto it in a way that does not return to the calling module/sub ??not
sure what to call it?? I think its a procedure, but that term gets thrown
around too much...

the Locking error, I will try to recreate it this afternoon if you can help
me to understand what was actually happening.. But that was the only thing I
changed and it went away, after the change. The error was a long one liner
about locking and the DB was open already in exclusive mode, and I did go
look in the folder while error was on screen, and there was no LDB file...

Here is a debugging copy of the bat file
rem @echo off

IF Exist "D:\Dev\SAVED_AdhocReporting.mdb" DEL
"D:\Dev\SAVED_AdhocReporting.mdb"

REN "D:\Dev\AdhocReporting.mdb" "SAVED_AdhocReporting.mdb"
pause

REN "D:\Dev\tmp.mdb" "AdhocReporting.mdb"
pause

rem exit

Thanks again very much Doug, I need to run to a meeting now, will be back
this afternoon.
==================

 

[Douglas J. Steele]

All you need to do is move the declaration for appAccess. The rest of the
code in sub DuplicateDB_Click remains unchanged.

 

[Douglas J. Steele]

Here is a debugging copy of the bat file
rem @echo off

IF Exist "D:\Dev\SAVED_AdhocReporting.mdb" DEL
"D:\Dev\SAVED_AdhocReporting.mdb"

REN "D:\Dev\AdhocReporting.mdb" "SAVED_AdhocReporting.mdb"
pause

REN "D:\Dev\tmp.mdb" "AdhocReporting.mdb"
pause

rem exit

If Len("D:\Dev\SAVED_AdhocReporting.mdb") > 0 Then
Kill "D:\Dev\SAVED_AdhocReporting.mdb"
End If

Name "D:\Dev\AdhocReporting.mdb" As "D:\Dev\SAVED_AdhocReporting.mdb"
Name "D:\Dev\tmp.mdb" As "D:\Dev\AdhocReporting.mdb"

 

[Guest]

DYNOMITE!!! Thank you so Much Doug

I've added

If Len(Dir(savedb)) > 0 Then Kill (savedb)
Name adhocdb As savedb
Name tmpdb As adhocdb

as you suggested and I am much happier with that then shelling out to run
BAT..
Everything works great right now, but I was thrown a new hitch in that
meeting.

Now it should be that the adhoc db needs to be closed before the user can
return to the first instance of access. I'm sure you have an easy answer for
me, but If I should start a new post for this, please let me know..

Also let me know if I should post the working code

Thanks again so much Doug!!!
=====================

 

[Douglas J. Steele]

I'd recommend starting a new thread, as I'm not sure I'll get to this today,
and others may not be reading this low in the thread.

I'd also recommend explaining why you're opening the second database
(something I never asked!). It's possible that what you're trying to do can
be achieved in a different manner.