Full Version: Close Access Database Using Vbscript
UtterAccess Forums > Microsoftģ Access > Access Forms
patriciaxxx
On unload of an access form I need to run a vbscript which closes the database. Both the VBA to run vbscript and code inside the vbs file must use relative path (ie the same path as the mdb resides in)

Iíve been trying to use Shell in the VBA and GetObject in the VBS file but I just canít get it to work with relative paths.

One final point if the mdb isínt open the vbs file should warn so and not run.
cheekybuddha
Why do you need a vbScript to do this?

Is there a reason why you can not just close the database from 'within'?

d
TheSmileyCoder
I wrote this blog post about how you can switch from 1 database to another by using a vbscript. You can find it here:
Switch Access Frontend without leaving access

You should be able to take the part about closing the "old" database, and adapt it to your needs.
patriciaxxx
Hello cheekybuddha

> Is there a reason why you can not just close the database from 'within

Iíve tried all the quits and closes I know of and all cause Access to Crash.

Iíve been looking at this for several days now and canít figure the crash out so the vbs file is the only way forward I can think of.

Just to note that the database isnít corrupted all service packs are installed this isnít that kind of issue. The quits and closes work perfectly on other forms and dbs. This form uses mediaplayecontrol it also closes ok with Application.Quit or DoCmd.Quit if you donít play a file. But if the player has been started and you quit then you get the crash and yes the player can be stopped first and every set object is set to nothing but you still get the crash but not using the vbs file that closes it ok albeit I cant figure out relative paths.
cheekybuddha
Ah, I see!

To set the vbs going you can use:
CODE
  Application.Followhyperlink CurrentProject.Path & "\yourScript.vbs"

Shell would probably work as well, but I don't know whether you need a path to the executable as well.

I'd imagine you'd need GetObject to find Access from the script. What have you tried so far?

d
cheekybuddha
Shell seems to bypass the pesky security warnings:
CODE
  Call Shell("C:\Windows\System32\wscript.exe " & Chr(34) & CurrentProject.Path & "\yourScript.vbs" & Chr(34))

I added Chr(34) in case there are spaces in your path.


hth,

d
patriciaxxx
Hello cheekybuddha

> To set the vbs going you can use:

I am trying this now?

> What have you tried so far?

strFile ="test.mdb"
dim fso, strFilePath
set fso = CreateObject("Scripting.FileSystemObject")
strFilePath = fso.GetAbsolutePathName(strFile)'


Dim db As Object

set db = GetObject(strFilePath)
db.quit
set db = nothing


set fso = nothing
patriciaxxx
TheSmileyCoder

Thank you will check it out

cheekybuddha

I tried Shell and put an echo in the vbs file to show me the file path. When I execute the vbs file from the folder it gives the correct full path and then closes the application, but when I shell it from VBA on unload of the form it gives the path as my documents and of course then it errors and doesnít close the application.
cheekybuddha
Are you able to adapt the code from here to check for running instances of Access and the application titles?
patriciaxxx
the code below closes the db but only if you click on the vbs file in the directory

can't find what to dim db as?

need it to run by calling it from access using relative path?

need message to show in vbs file if database isn't open and file not to execute?

CODE
strFile = "wmp.mdb"
dim fso, strFilePath
set fso = CreateObject("Scripting.FileSystemObject")
strFilePath = fso.GetAbsolutePathName(strFile)

Dim db

set db = GetObject(strFilePath)
db.quit
set db = nothing

set fso = nothing
cheekybuddha
Better yet, you can pass the path as a variable to the vbScript! See here

yourScript.vbs - saved in sam folder as your mdb
CODE
Dim db As Object, strFilePath

strFilePath = WScript.Arguments(0)
set db = GetObject(strFilePath)
db.quit
set db = nothing

Called with:
CODE
  Call Shell("wscript.exe " & Chr(34) & CurrentProject.Path & "\yourScript.vbs" & Chr(34) & " " & _
             Chr(34) & CurrentProject.Path & "\" & CurrentProject.Name & Chr(34))


hth,

d
patriciaxxx
> Better yet, you can pass the path as a variable to the vbScript!

this did sound good just tried it but it didnt work gave no errors im guessing maybe the quotes are not quite right i know they can be tricky?

any ideas
cheekybuddha
Hmmm... I put a MsgBox to see the value of strFilePath and it showed up correctly. I'm not in front of the computer at the moment.

What do you get if you echo or MsgBox strFilePath ?
patriciaxxx
MsgBox Chr(34) & CurrentProject.Path & "\vbs.vbs" & Chr(34) & " " & _
Chr(34) & CurrentProject.Path & "\" & CurrentProject.Name & Chr(34)

if i run the above from access i get what you would expect ie

"fullpathandname" "fullpathandname"

when i call this from the access form i see something open and close quick but nothing happens to the access database?

Call Shell("wscript.exe " & Chr(34) & CurrentProject.Path & "\test.vbs" & Chr(34) & " " & _
Chr(34) & CurrentProject.Path & "\" & CurrentProject.Name & Chr(34))

when i double click on the vbs file i get the error subscript out of range
cheekybuddha
Ok, I can't test for a while.

Try putting all the 3 shell parts in to variables.

If you have spaces in the path then make sure you're concatenated double quotes around each bit.

Put together a shell string of the 3 parts separated by spaces.

Debug.Print the shell string. Post here.

Also post your vbs file contents.

d
patriciaxxx
i call this from access

CODE
Call Shell("wscript.exe " & Chr(34) & CurrentProject.Path & "\test.vbs" & Chr(34) & " " & _
             Chr(34) & CurrentProject.Path & "\" & CurrentProject.Name & Chr(34))


this is the test.vbs contents

CODE
Dim strFile
Dim strFilePath

strFilePath = WScript.Arguments(0)
set strFile = GetObject(strFilePath)
strFile.quit
set strFile = nothing


im not sure how to do the rest

critically the flashing i noticed is in fact the script running (no errors) the database seems to close and then immediately open again?
cheekybuddha
Put a MsgBox after strFilePath = WScript.Arguments(0) in the script
CODE
MsgBox strFilePath



If the db closes, that's good isn't it?

Don't know why it would open again! shrug.gif

d
patriciaxxx
> Put a MsgBox after strFilePath = WScript.Arguments(0) in the script

did this and the message box gives the full path as one would expect then the db closes (good) and re-opens (not good) then message, and so on...

> If the db closes, that's good isn't it

it would be if it worked properly, as it is its not good at all

it causes the application to go into some kind of crazy open and close and message box loop and the only way to exit is to end program and that causes a crash.
cheekybuddha
OK, this worked for me:
CODE
Function fRemoteCloseDB() As Boolean

  Dim strShell As String, _
      strVbs As String, _
      strThisDB As String
  Const c_WSCRIPT As String = "wscript.exe"
  
  With CurrentProject
    strVbs = Chr(34) & .Path & "\test.vbs" & Chr(34)
    strThisDB = Chr(34) & .Path & "\" & .Name & Chr(34)
  End With
  strShell = c_WSCRIPT & " " & strVbs & " " & strThisDB
  Debug.Print strShell
  Stop
  fRemoteCloseDB = Shell(strShell)
  
End Function

test.vbs:
CODE
Dim db
Dim strFilePath

strFilePath = WScript.Arguments(0)
MsgBox strFilepath
set db = GetObject(strFilePath)
db.quit
set db = Nothing


The re-opening bit is a complete mystery! Might you have any stray code anywhere that might cause this?


d
cheekybuddha
The Stop command is so that I could see the debug.print output. You will need to hit F8 when the code stops there.
cheekybuddha
Can you attach a copy of your db, or a copy with just the form and the activex in it, so I can try it from this end?
cheekybuddha
Anders, I didn't see your post earlier, but have just read your link; and I see you link to the same StackOverflow solution for passing arguments to vbscript that I linked to in post #11

thumbup.gif

d
patriciaxxx
Hello David

I have attached a mediaplayer control example I found and placed Application.Quit on the unload event (makes no difference if you put it on the close even) for you to try. Please do play a file on it and then close the form using the red X close button and watch the db crash because I would really like to know why it does this and what needs to be done to stop it?

Just tried your code and it works for me.

Using this vbs file like this does close the database and doesnít throw up the crash that I was getting with Application.Quit, so I workaround I think.

One final note on the crash, out of curiosity that I may have been doing something wrong, I downloaded the mediaplayer control example from this site and placed Application.Quit on the form close event and guess what, it crashed?

I want to say a big thank you for sticking with me on this and seeing me through to some working code and a solution.

> Might you have any stray code anywhere that might cause this

When I was using the vbs file with full (not relative) path I had Cancel = True on the forms unload event before the code which called the vbs file. I may have been using Set objAccess = GetObject(, "Access.Application") in the vbs file at some point also, I tried so many things, anyway whatever I had did work but didnít use relative path which was what I wanted.

Although Cancel = True didnít affect this situation it seems to be responsible for the database closing and then reopening with your relative path code because now that I have removed it your code works fine. Interesting.

Many thanks again,
patriciaxxx
Hello TheSmileyCoder

> I wrote this blog post about how you can switch from 1 database to another by using a vbscript. You can find it here:

I looked here in more detail which led me to your ďAccess Crash ReporterĒ, very niceÖ I complement you. Unfortunately I had some bother running it and hoped you could help me sort it?

I noticed a MISSING reference to Microsoft office 14 Access database engine object library? which I could not find in my list? so I removed it? then of course the code wont compile. I noticed DAO is used so set reference to Microsoft DAO 3.6 object library? code now compiles no errors.

However when I come o use the following form
TSC_ErrRep_frm_SubmitError
Access to crashes.

I tried opening the form in design view to look at the code and noticed that when I then close it Access crashes again?

Any ideas what Iím doing wrong? I followed all your instructions to the letter.

Iím using access 2002 and 2003.
cheekybuddha
Wow! It's enough to drive you mad, isn't it!! pullhair.gif

There is some timing issue, to do with removing the WMP object from memory - I didn't figure it out. I tried all sorts of stuff like unloading playlists etc, but you could go on for hours trying to find the problem

The best I could do was to make a new form (frmQuit), with the following code in its Timer event:
CODE
Private Sub Form_Timer()

  Do While CurrentProject.AllForms("Media Player").IsLoaded
    DoEvents
  Loop
  Application.Quit
  
End Sub

Then, in the Unload of form 'Media Player'
CODE
Private Sub Form_Unload(Cancel As Integer)
    
    'Close the Windows Media Player Control (Free Memory)
    Me.WindowsMediaPlayer1.Close
    DoCmd.OpenForm "frmQuit", acNormal, , , , acHidden
    With Forms.frmQuit
      .Visible = False
      .TimerInterval = 1000
    End With
    
End Sub

You can try reducing the TimerInterval more, but at 200 it crashed.

I tried using Sleep API in the Media Player form's Unload event to negate the need for a separate form to quit Access, but it still crashed shrug.gif

hth,

d
patriciaxxx
> Wow! It's enough to drive you mad, isn't it

Yes, it most certainly is. Iím glad though that it crashed for you just to prove the fault was real and not something I was doing my end.

> I tried all sorts of stuff
Yes. me too, but unfortunately I couldnít sort it either?

I really appreciate you looking at it though and giving me the benefit of your experience and input and also for your clever workaround.

Thank you again for your continued help and support.
patriciaxxx
Hello David

Just a quick note to say I tried your frmQuit solution and it does a good job.

> You can try reducing the TimerInterval more, but at 200 it crashed

I could get as low as you can get without effectively turning the timer off ie 0?

In other words I set the timer interval to 1, yes just 1 as in 1 millisecond, and no crash?

None of it makes any sense to me?
cheekybuddha
I'm glad the workaround solves it for you! It beats having to maintain a separate .vbs file.

I'm surprised we couldn't turn up more cases of this issue. shrug.gif

Good luck with your project! thumbup.gif


d
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.