?
Solved

Access 2010 Compact and repair in middle of running code

Posted on 2013-05-29
1
Medium Priority
?
1,117 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 59

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

569 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