LeLeBrown
asked on
Access 2010 Compact and repair in middle of running code
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\To OffBackend .accdb"), False
accapp.Visible = False
accapp.DoCmd.RunMacro "MacroSetup"
accapp.CloseCurrentDatabas e
accapp.DoCmd.Quit
'Set accapp = Nothing
'COMPACT AND REPAIR REMOTE DB
sDataFile = "C:\Users\LB\Documents\ToO ffBackend. accdb"
sDataFileTemp = "C:\Users\LB\Documents\ToO ffBackendT emp.accdb"
sDataFileBackup = "C:\Users\LB\Documents\ToO ffBackendB ackup " & 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\To OffBackend .accdb"), False
accapp.Visible = False
accapp.DoCmd.RunMacro "MacroSetup2"
accapp.CloseCurrentDatabas e
accapp.DoCmd.Quit
Set accapp = Nothing
End Function
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
accapp.Visible = False
accapp.DoCmd.RunMacro "MacroSetup"
accapp.CloseCurrentDatabas
accapp.DoCmd.Quit
'Set accapp = Nothing
'COMPACT AND REPAIR REMOTE DB
sDataFile = "C:\Users\LB\Documents\ToO
sDataFileTemp = "C:\Users\LB\Documents\ToO
sDataFileBackup = "C:\Users\LB\Documents\ToO
'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
accapp.Visible = False
accapp.DoCmd.RunMacro "MacroSetup2"
accapp.CloseCurrentDatabas
accapp.DoCmd.Quit
Set accapp = Nothing
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.