some questions about BLOATING 1

[VickyC]

hello

I'm working on a project that involves manipulation of very large tables, and I'm having major BLOAT issues. This is part of a mathematics research project (my background is definitely not in programming!) where I need to do a great deal of DELETING and APPENDING. I've spent a good deal of time researching causes of bloat and have avoided the usual suspects wherever possible: (recordsets closed when done, optimized data types, using skeleton tables instead of creating/deleting new ones, etc...). I believe that the DELETING and APPENDING that I do is unavaidable.

My project involves generating and 'crunching' a series of very large tables to produce a relatively small (2 to 3 MB) output table. This entire process is repeated thousands of times. My application works very well, but I need to stop to COMPACT at the mid-point and the end of every cycle (about every 6.5 minutes), or else I hit the 2.0 GB barrier. In a perfect world, I'd like to run the process through the night without my intervention, but I'm realizing this may be impossible in MS ACCESS.

As background info, my application has no forms, and it operates on a single machine with one user (um... that's me). There are no security concerns.

Here are a few points where I'm hoping for some guidance.

1) Is there some way to use script to stop the application at certain points, COMPACT, then restart?

2) In several places I've seen advice to used stored queries instead of generating them in VBA. I've tested this and haven't found much difference. Any comments?

3) Is there any advantage in splitting the application? Does this even make sense on a single, one-user machine? Does this have any effect on bloating?

4) Would migrating to, say, SQL Server help me get around this bloating problem?

Much thanks in advance for any assistance
Vicky C.

 

[dhookom]

I would definitely consider moving to SQL Server or SQL Server Express which you can install on a workstation.

When I have a lot of requirements to create and delete lots of "temporary" tables, I will create a new, temporary MDB to pass records through. This leaves me with front-end, back-end, and temporary files. You could also create multiple temporary back-ends if needed.

Duane
Hook'D on Access
MS Access MVP

 

[VickyC]

Duane - thanks for responding. I may need a bit of clarification.

You mentioned a 'workstation'. If I use SQL Server or SQL Server Express, can it be installed on a single computer along with Access? Does it make sense to have a FE and a BE on the same machine? I've not done this before.

When you mentioned a temporary MDB to pass records through, exactly how does this help with the bloating issue?

many thanks
Vicky C.

 

[dhookom]

I have SQL Server Express installed on my laptop. You don't need to have a server. You can keep all of your files/databases on your PC.

The temporary MDB is created when the application opens, tables are created and linked as needed, records are added & updated, and deleted when no longer needed.

The code I use to create a new file is:

[green]'---------------------------------------------------------------------------------------
' Procedure : CreateMDB
' Author    : dhookom
' Date      : 10/5/2009
' Purpose   : Create new mdb for temporary tables for reporting or importing
'---------------------------------------------------------------------------------------
'[/green]
Public Function CreateMDB(strMDBName As String) As Boolean
   On Error GoTo CreateMDB_Error

    CreateMDB = False
    Dim db As DAO.Database
    If Len(Dir(strMDBName)) > 0 Then
        Kill strMDBName
    End If
    Set db = CreateDatabase(strMDBName, dbLangGeneral)
    Set db = Nothing
    CreateMDB = True

   On Error GoTo 0
   Exit Function

CreateMDB_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CreateMDB of Module modNewDatabase"
    
End Function

I have another function that reads table structure from a table to create and link empty tables.


Duane
Hook'D on Access
MS Access MVP

 

[VickyC]

Thank you, Duane, for some great info!

I'm struggling a bit, because these procedures are new to me. Is the idea that you would use Access as a FE, tables in SQL Server Express as a BE, then create a temporary mdb file to hold temporary files, with all three of these components on the same computer in my case? Then BLOAT, which is at the core of my current problem, would occur in the Temp file which would be deleted and recreated as necessary. Is that the basic idea?

The function CreateMDB looks great. Regarding the 'read table structure' function you mentioned, would it be reading tables in the BE, then creating temp copies in the Temp mdb file?

These ideas sound like they may lead to resolution of my BLOAT problem, but I'm still struggling to understand how the 3 parts (FE, Temp db, BE) work together.

Many thanks for the advice you've offered.

Vicky C.

 

[dhookom]

If you use SQL Server Express, I would not use a temporary Access file. I would place all data in SQL Server tables.

The following line should give you an idea of how I store information about the tables in the temporary MDB file. I recreate a recordset and loop through it to build tables. I run the code to create the tables after the code to create a temp mdb.

    strSQL = "SELECT ddtMDBName, ddtTableName, ddtFieldName, ddtFieldType, ddtFieldSize " & _
        "FROM ztblDataDefinition ORDER BY ddtMDBName, ddtTableName, ddtOrder, ddtFieldName"

Duane
Hook'D on Access
MS Access MVP

 

[VickyC]

Duane - great advice. So by using an Access FE and SQL Server Express BE holding ALL data, does that settle the bloating issue. Is 'Express' able to recover space when, say, a table's records are deleted in a way that Access cannot?

Great thanks for your help so far!
Vicky c

 

[dhookom]

Check out some SQL Server Express specifications. As noted you can create many databases in a single instance of the server. I haven't experienced issues with not being able to record space when tables have been deleted/dropped.

Duane
Hook'D on Access
MS Access MVP