Solved

Access 2010 Compact and repair in middle of running code

Posted on 2013-05-29
1
991 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now