Solved

Access 2010 Compact and repair in middle of running code

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

910 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

21 Experts available now in Live!

Get 1:1 Help Now