Solved

Access 2010 Compact and repair in middle of running code

Posted on 2013-05-29
1
1,002 Views
Last Modified: 2013-05-29
I have a backend database that connects to AS400 and SQL server and builds tables daily. I have added some code and this has made the backend too large to continue processing in the middle of the run. I created another Access DB to open the backend and run the code in two separate steps with a compact/repair in the middle. The code to initially open the backend and run some code and then compact runs fine. when it tries to open the backend a second time, I get an error. Can you look at the code below and tell me if I am closing the backend properly before opening it again? I didn't think the compact/repair leaves it open. At least it has no lock file out there when it ends.

Function RunBackend()

Dim cd As DAO.Database
Dim accapp As Access.Application
Dim sDataFile As String, sDataFileTemp As String, sDataFileBackup As String
Dim s1 As Long, s2 As Long
Set accapp = New Access.Application

Set cd = CurrentDb
accapp.OpenCurrentDatabase ("C:\Users\LB\Documents\ToOffBackend.accdb"), False
accapp.Visible = False
accapp.DoCmd.RunMacro "MacroSetup"
accapp.CloseCurrentDatabase
accapp.DoCmd.Quit
'Set accapp = Nothing


'COMPACT AND REPAIR REMOTE DB

sDataFile = "C:\Users\LB\Documents\ToOffBackend.accdb"
sDataFileTemp = "C:\Users\LB\Documents\ToOffBackendTemp.accdb"
sDataFileBackup = "C:\Users\LB\Documents\ToOffBackendBackup " & Format(Now, "YYYY-MM-DD HHMMSS") & ".accdb"
'get file size before compact
Open sDataFile For Binary As #1
s1 = LOF(1)
Close #1

'backup data file
FileCopy sDataFile, sDataFileBackup

'only proceed if data file exists
If Dir(sDataFileBackup, vbNormal) <> "" Then
        'compact data file to temp file
        On Error Resume Next
        Kill sDataFileTemp
        On Error GoTo 0
        DBEngine.CompactDatabase sDataFile, sDataFileTemp

        If Dir(sDataFileTemp, vbNormal) <> "" Then
            'delete old data file data file
            Kill sDataFile

            'copy temp file to data file
            FileCopy sDataFileTemp, sDataFile

            'get file size after compact
            Open sDataFile For Binary As #1
            s2 = LOF(1)
            Close #1
           
        Else
        End If

Else
End If


Set cd = CurrentDb
accapp.OpenCurrentDatabase ("C:\Users\LB\Documents\ToOffBackend.accdb"), False
accapp.Visible = False
accapp.DoCmd.RunMacro "MacroSetup2"
accapp.CloseCurrentDatabase
accapp.DoCmd.Quit
Set accapp = Nothing


End Function
0
Comment
Question by:LeLeBrown
1 Comment
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39204483
<<when it tries to open the backend a second time, I get an error>>

  You have to open an instance of Access again:

Set accapp = New Access.Application

You told it to quit:

accapp.DoCmd.Quit

in the first pass.

Jim.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
My experience with Windows 10 over a one year period and suggestions for smooth operation
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question