2

How can I use VBA to restart Microsoft Access 2007 from within the same DB file..??

I'm developing a DB that will eventually be packaged & distributed with the Runtime. For development I'm changing various UI settings, and renaming the file back & forth between *.accdb and *.accdr. But I would like to use a fully programmatic method, which I would then assign to a button or keystroke.

But as anyone who's tried can tell you, one cannot easily use VBA to restart Access from within the same DB. I tried the code here: MS Access: how to compact current database in VBA, but I received the error message "You cannot compact the open database by running a macro or Visual Basic code."

enter image description here

I have various half-baked ideas how to "bounce" the restart off a VBS script, CMD file, or another Accdb file, but I wanted to ask who else may have done this successfully..??

What variety of ways have others done this with success..??

spinjector
  • 3,121
  • 3
  • 26
  • 56
  • 3
    When I needed to compact my database on a regular basis, I put the main tables in one database and just used a second one as the front-end including the macros (which could obviously compact the "data" database). – YowE3K Aug 07 '17 at 00:46
  • Why are you using 2007? – tahwos Aug 07 '17 at 02:46
  • 2007 is what we have, budget does not allow for a big spend for a newer version, and 2007 works adequately for our needs. – spinjector Aug 07 '17 at 13:23

2 Answers2

2

I had the following procedure to copy new version of frontend and then open. Unfortunately, had to abandon when IT tightened security and can no longer programmatically copy files. Don't ask me why it works, it was found code. Previously, I had VBA call a VBScript which would do the copy and reopen.

Private Sub Form_Load()
'Check for updates to the program on start up - if values don't match then there is a later version 
If Me.tbxVersion <> Me.lblVersion.Caption Then
    'because administrator opens the master development copy, only run this for non-administrator users
    If DLookup("Permissions", "Users", "UserNetworkID='" & Environ("UserName") & "'") <> "admin" Then
        'copy Access file
        CreateObject("Scripting.FileSystemObject").CopyFile _
            gstrBasePath & "Program\Install\MaterialsDatabase.accdb", "c:\", True
        'allow enough time for file to completely copy before opening
        Dim Start As Double
        Start = Timer
        While Timer < Start + 3
            DoEvents
        Wend
        'load new version - SysCmd function gets the Access executable file path
        'Shell function requires literal quote marks in the target filename string argument, apostrophe delimiters fail, hence the quadrupled quote marks
        Shell SysCmd(acSysCmdAccessDir) & "MSAccess.exe " & """" & CurrentProject.FullName & """", vbNormalFocus
        'close current file
        DoCmd.Quit
    End If Else
    'tbxVersion available only to administrator to update version number in Updates table
    Me.tbxVersion.Visible = False
    Call UserLogin End If
End Sub
June7
  • 19,874
  • 8
  • 24
  • 34
1

I use the utility provided here: http://blog.nkadesign.com/2008/ms-access-restarting-the-database-programmatically/

In a nutshell, when you run this, Access will quit. However, before it quits it will create a small batch file in the same folder as the database with a couple of commands in. Access then quits, and the batch file will then wait for the removal of the lock file (laccdb file). Once that's removed (as it should be if you're the only user in the database), the batch file then restarts the application.

There is also an option to compact the database on close, too. It works incredibly well for me in my environment, and I have successfully used this for probably somewhere in the region of 5 years.

AdzzzUK
  • 205
  • 1
  • 5