1

I am running a Sub procedure stored in an ACCESS database using VBScript. The procedure queries the database and exports/saves a CSV file.

My problem is that the script successfully opens the database, runs the procedure but then leaves ACCESS open because ACCESS opens a prompt asking "Are you sure that you want to leave ACCESS" (rather its equivalent in German). I want it to close without interaction.

The basic idea of this script is to run it via the Windows Task Scheduler. (Which does not work right now but that is another question.)

Here is the part of my VBScript dealing with the ACCESS database:

Dim objAccess
Set objAccess = createObject("Access.Application")
objAccess.OpenCurrentDataBase("C:\FiselTools\Allgemein\Scripte\Pellenc-CopyBelegarchiv3.accdb")
objAccess.Run "CopyBelegarchiv"
objAccess.Quit acQuitSaveAll
Set objAccess = Nothing

Using this script manually, it does open the database, export the file, finally executes the part following the code from above - so only closing ACCESS does not work and my guess is that it's not a problem with the script or the procedure but the ACCESS.

HBB-ThinkTank
  • 39
  • 6
  • 1
    By default, Access displays no such prompt. That probably means you have VBA code somewhere that's responsible for this prompt. Try the same thing on a database without any VBA. – Erik A Jan 19 '21 at 13:05
  • You did not define the variable `acQuitSaveAll`. I'm guessing that is one of the Access constants? You should look it up, and define it as a constant yourself. – Geert Bellekens Jan 19 '21 at 18:29
  • Let me start with acQuitSaveAll: it comes from https://learn.microsoft.com/en-us/office/vba/api/access.application.quit and I found it used inside the VBScript on some forum (can't find the link right now) but I've added it in the hope that it would solve my problem. Actually the same problem occurs without this variable. Which leaves me with the idea from @erika-a but I could not find that code. I did not create this database. Could anyone give me a clue what the VBA code might look like? Thanks in advance! – HBB-ThinkTank Jan 19 '21 at 23:22

1 Answers1

1

Erik A basically gave the right answer: there was some VBA code that is responsible for this prompt (see below). It was part of the primary form.

So in this case I found two possible solutions:

  1. Do not display/show the form (which worked for me as I made a copy of the file dedicated for just this purpose).
  2. If you need the form then you might need to delete the VBA code.
Private Sub Form_Unload(Cancel As Integer)

Dim Answer As Variant
Answer = MsgBox("Wollen Sie die Anwendung wirklich beenden?" & vbNewLine & vbNewLine & "Nicht gespeicherte Änderungen gehen dabei möglicherweise verloren", vbQuestion + vbYesNo + vbDefaultButton2)
If Answer = vbNo Then
Cancel = True
Else
DoCmd.Quit acQuitSaveNone
End If

End Sub

Since it is part of the Access class object of the form that is configured to be displayed on opening the ACCESS database and I do not need the form when I run the procedure via VBScript I simply changed the database to not display any form when the database is opened.

Some day I will remove any form, query, and any other object not needed from this special copy of the database that I don't need to run from VBScript.

Thanks!

HBB-ThinkTank
  • 39
  • 6