Access Database Approaches 2 GIG limit

[Seti]

I have an access database that runs about 500 megs once compacted. However, I have a ton of macros that create temporary tables. These tables run about 500 megs each. I delete the temp tables after I am done with each 500 meg table (there are 3 such beasties), but access does not release the space so I am nearing/exceeding the 2 gig limit. Note that I am writing my temp results to the same table each time. I would have thought that this would have simply overwritten the previous stuff rather than creating new stuff (sorry for the technical use of language).

Is there any way to compact the database or reclaim the space from a macro? The only solution I have so far is to run one macro to create the temp table and process it, then delete the temp table, then compact the database and then start all over again with the next macro. The problem is that I am developiong this for someone else and would like it to be a little more idiot proff than this.

Also, compacting takes 10-15 minutes depending on which machine I use (1.8 GHz or 1.0 GHz). Any ideas would be great.

TIA.

 

[lord of the badgers]

is there anyway you could perhaps send the temp tables to another database? just a (largely rubbish no doubt) thought...!

 

[bat17]

You should be able to use linked tables to do it.
Are You actualy deleting the tables or just deleting the data out of them?

Peter

 

[Seti]

Peter,

I am deleting the tables, not just the data in them. I am currently trying out an option where I have my make table query simply overwrite the existing table when I run a second query hoping this will save some space. The problem is it takes a couple of hours to see if there is any difference and I am not quite there yet.

Another solution I am pondering is LotB's option to have the make table query output the results to another database and then link back to my current one to run the next step of the project. I just need to find a simple way to manage the whole process. I am thinking some VBA might hold the answer.

Thanks for the suggestions.

Seti

 

[bat17]

Deleting just the data rather than the table should save on the bloat I think.
If you are running A2K or above I belive that you can compact with a vba command but I dont have it here to check.

Depending on how you are writing the data you may find it easier to work to a table linked to another database than trying Make a table and then link it back.

Peter

 

[lord of the badgers]

you can compact from within the dbase in A2k but it's not as simple as that... can't remember where i nicked this from (sorry!)

Function CompactDB() 'compacts database from within database
CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
accDoDefaultAction
End Function

or, in A2k you can set Compact on Exit from the Tools Menu, but i do think this doesn't solve the poster's issue...

 

[pastels]

Is there such a function I can use for Access97?

Thanks

 

[mdmilner]

Source for above was probably here:
http://www.mvps.org/access/general/gen0041.htm

This will do Access97 - it's not simple though.
http://www.mvps.org/access/modules/mdl0030.htm

Here's what I like to do:

If ObjectExists("Table", "tblName") Then
    DoCmd.DeleteObject acTable, tblName
End If
Function ObjectExists(ByVal strObjectType As String, _
                ByVal strObjectName As String) As Boolean

     Dim db As Database
     Dim tbl As TableDef
     Dim qry As QueryDef
     Dim i As Integer
     
     Set db = CurrentDb()
     ObjectExists = False
     
     If strObjectType = "Table" Then
          For Each tbl In db.TableDefs
               If tbl.Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next tbl
     ElseIf strObjectType = "Query" Then
          For Each qry In db.QueryDefs
               If qry.Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next qry
     ElseIf strObjectType = "Form" Or strObjectType = "Report" Or strObjectType = "Module" Then
          For i = 0 To db.Containers(strObjectType & "s").Documents.Count - 1
               If db.Containers(strObjectType & "s").Documents(i).Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next i
     ElseIf strObjectType = "Macro" Then
          For i = 0 To db.Containers("Scripts").Documents.Count - 1
               If db.Containers("Scripts").Documents(i).Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next i
     Else
          MsgBox "Invalid Object Type passed, must be Table, Query, Form, Report, Macro, or Module"
     End If

Set db = Nothing
End Function

Could always add this kind of line into your code to purge the records.

Dim dbs as DAO.Database
Dim strSQL As String

Set dbs = CurrentDb()
strSQL = "DELETE * FROM tblName"
DoCmd.RunSQL strSQL

Mike

 

[mdmilner]

Here's a nosy question.

Why are you creating multiple temporary tables anyways?
Does any of the data from the original table get pasted into the temp table?
Does any of the data from the 1st/2nd/3rd table get carried over to a succeeding table?

If yes, it might be less space/time/cpu intensive to create a 2nd table and link it to the first via a relationship instead.

Mike