UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Are Local Temp Tables Causing Your Fe Database To Bloat?, Access 2010    
 
   
bgmsd
post Oct 31 2014, 04:52 PM
Post#1



Posts: 670
Joined: 31-July 11



Our back-end is on a slow network and we have a report sub where, depending on the criteria chosen, the user could potentially generate up to 1500 single page pdf report files into a folder on their Desktop. The reports have to access several tables many times and if they are on the slow server, then the reports take a very long time to generate. So, to solve that problem, I copy the linked tables from the back-end to the front end as local tables and pre-pend Copy_ to them to distinguish them as local tables.

What I discovered today was that while generating all of the reports and moving a lot of data into and out of the temp tables, the front end was becoming fragmented. When the reports were about 92% complete, I have a progress bar, the database was at 2GB, and when it is compacted it is is only 37MB. So, I decided that what I was going to do was to create a temp database in the same folder as the front end and make all of the Copy_ tables go to the temp database as linked tables. Then, I could monitor the size of the temp database and if it gets to big, pause the reports with a message box and inform the user that the Temp database is going to be compacted and then the reports would resume generating. Once the reports are complete, I could delete the linked tables, and then delete the temp database.

Here is the code:

CODE
Dim strPath As String
Dim sDataFile As String, sDataFileTemp As String, sDataFileBackup As String
Dim s1 As Long, s2 As Long
Dim strPath As String

Sub copyAllLinkedTables()
Dim strLinkedTable As String
Dim strNewTable As String
Dim t As TableDef

strPath = Environ("USERPROFILE") & "\Documents\"

DoCmd.SetWarnings False
For Each t In db.TableDefs
    'These are the tables that will be in your external database. Set your criteria accordingly
    If (Right(t.Name, 7) = "History" Or t.Name = "NWA" Or Mid(t.Name, 3, 3) = "tbl" _
    Or t.Name = "qry_to_xls_Laborcharges" Or t.Name = "tblAllocatedHistory" Or t.Name = "tblBalanceHistory" _
    Or t.Name = "tblQryToXLS" Or t.Name = "tblQryNonLabor") And Left(t.Name, 4) <> "Copy" Then
        strLinkedTable = t.Name
        strNewTable = "Copy_" & t.Name
        'Copy data into the external database  
        DoCmd.RunSQL "SELECT [" & strLinkedTable & "].* INTO [" & strNewTable & "] IN '" & strPath & "Temp.accdb' FROM [" & strLinkedTable & "];"
    End If
Next
DoCmd.SetWarnings True
Set db = CurrentDb
End Sub

Sub DeleteTempTables()
Dim t As TableDef

For Each t In db.TableDefs
    If Left(t.Name, 4) = "Copy" Then
        DoCmd.DeleteObject acTable, t.Name
    End If
Next
Set db = CurrentDb
End Sub

Public Sub LinkTables(strBaseTable As String)
strPath = Environ("USERPROFILE") & "\Documents\Temp.accdb"

On Error GoTo CreateAttachedError

Dim tdf As TableDef
Dim myDB As Database

    DoCmd.SetWarnings False
    Set myDB = CurrentDb
    Set tdf = myDB.CreateTableDef(strBaseTable)
    
    With tdf
        .Connect = ";DATABASE=" & strPath
        .SourceTableName = strBaseTable
    End With
    
    myDB.TableDefs.Append tdf
        
    DoCmd.SetWarnings True

CreateAttachedExit:
    Exit Sub

CreateAttachedError:
        
    If Err.Number = 3110 Then
        Resume CreateAttachedExit
    Else
        If Err.Number = 3011 Then
            Resume Next
        End If
    End If
End Sub

Public Sub CreateLinkedTables()
Dim ExtDB As DAO.Database
Dim tdf As DAO.TableDef
Set ExtDB = OpenDatabase(Environ("USERPROFILE") & "\Documents\Temp.accdb")

For Each tdf In ExtDB.TableDefs
    If Left(tdf.Name, 4) = "Copy" Then
        LinkTables (tdf.Name)
    End If
Next

ExtDB.Close
Set tdf = Nothing
Set ExtDB = Nothing
End Sub

Public Sub CompactMe()
strPath = Environ("USERPROFILE") & "\Documents\"
sDataFile = strPath & "Temp.accdb"
sDataFileTemp = strPath & "TempTemp.accdb"
sDataFileBackup = strPath & "TempBackup.accdb"


'get file size before compact
Open sDataFile For Binary As #1
s1 = LOF(1)
Close #1

'backup data file
'FileCopy sDataFile, sDataFileBackup
CopyMe

'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
            
            MsgBox "Compact complete " & vbCrLf & vbCrLf _
                & "Size before: " & Round(s1 / 1024 / 1024, 2) & "Mb" & vbCrLf _
                & "Size after:    " & Round(s2 / 1024 / 1024, 2) & "Mb", vbInformation
        Else
                        MsgBox "ERROR: Unable to compact data file"
        End If

Else
                MsgBox "ERROR: Unable to backup data file"
End If


End Sub

Public Sub CopyMe()
Dim fs As Object

Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile sDataFile, sDataFileBackup
Set fs = Nothing

End Sub

Public Sub CreateDatabaseDAO()
    'Purpose: create temp database
    Dim dbNew As DAO.Database
    Dim strFile As String
    
    'Create the new database.
    strFile = strPath & "Temp.accdb"
    Set dbNew = DBEngine(0).CreateDatabase(strFile, dbLangGeneral)
        
    'Clean up.
    dbNew.Close
    Set dbNew = Nothing
    Debug.Print "Created " & strFile
End Sub

Public Sub KillTempDatabase()
On Error Resume Next
Kill Environ("USERPROFILE") & "\Documents\Temp.accdb"
Kill Environ("USERPROFILE") & "\Documents\TempBackup.accdb"
End Sub

'Call this sub before you start operations that will require your database to use the temp tables
Public Sub CreateAndPopulateTempDatabase()
CreateDatabaseDAO
copyAllLinkedTables
CreateLinkedTables
End Sub

'Call this sub when you are finished with your temp tables
Public Sub DeleteTempDatabase()
DeleteTempTables
KillTempDatabase
End Sub

'Call this sub periodically from your reports module or whatever code causes your database to grow rapidly
Public Sub CheckIfCompactNeeded()
strPath = Environ("USERPROFILE") & "\Documents\"
sDataFile = strPath & "Temp.accdb"

'get file size before compact
Open sDataFile For Binary As #1
s1 = LOF(1)
Close #1

If Round(s1 / 1024 / 1024, 2) > 1500 Then '1.5GB I think
    MsgBox "The Temp Database that is used to store local data is nearing it's 2GB limit. The database will now be compacted and then your reports will resume generating."
    CompactMe
End If
End Sub


Sincerely,

David
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 05:32 PM