Re: Mermory use

My suggestion would be to create the temporary table in a temporary database. By deleting and then re-creating a database, you never have to be concerned with compacting. See the CreateDatabase function below for VBA code to accomplish this.

Public Function CreateDatabaseX(db As String) As Boolean

     'Purpose: Create a new database from code, first deleting any prior version

     'Parameters: db = full path for database, e.g. f:\data\tempdata.mdb

    Dim wrkDefault As Workspace, dbsNew As Database

    CreateDatabaseX = True
    On Err GoTo err_CreateDatabaseX
    ' Get default Workspace.
    Set wrkDefault = DBEngine.Workspaces(0)

    ' Make sure there isn't already a file with the name of     ' the new database.
    If Dir(db) <> "" Then

        Kill db
    End If
    ' Create a new database
    Set dbsNew = wrkDefault.CreateDatabase(db, dbLangGeneral)     dbsNew.Close

exit_CreateDatabaseX:

    Exit Function

err_CreateDatabaseX:

    MsgBox Error$
    CreateDatabaseX = False
    Resume exit_CreateDatabaseX

End Function

You can then use SELECT INTO (a make-table query) to build the table in the temp db. Something like this will work:

        sMdbOut = "f:\data\tempdata.mdb"
        strSQL = "Select * " _
            & " Into tblRpt IN '" & sMdbOut & "'" _
            & " from qryRpt "
        CurrentDB.execute sql,dbfailonerror

You can then attach to the temp table in the external db to execute the queries that you use to update your data.

Hope this helps.

Allan Thompson
FieldScope LLC
www.fieldscope.com

"R. Groot Beumer" <Info_at_RGBplus.nl> wrote in message news:Kof07.172415$u5.3410087_at_zwoll1.home.nl...
> Hello,
>
> I've made a database which updates, adds en deletes a great ammount of
 data.
> To make a more step-wise approach possible I used a temporary table where
> the data is put into and adjusted (through several queries). After that
 the
> correct data is send to the correct table and the values in the temp-table
> are deleted. Because the data (about 7000 rows a time) will be updated
> frequently, the databasesize grows quickly. After a double update (read
> values from an extern source) session the database size has grwon from 4
 to
> over 140 MB.
> I now the best way to reduce size is to Compact the database. But is there
 a
> syntax in VBA to automaticly do this after the reading proces (while being
> in the program). My second question is: Is this a save sollution. My
> experience is that the compacting
> My experience with compacting such a large database is not very good. Many
> times the computer just runs dead while compacting (perhaps because of
 other
> running programs).
>
> How comes the database doesn't reduce (much) in size while deleting a
 great
> ammount of data.
>
> Thanx for any help.
>
> Remco (Holland)
>
>
Received on Sun Jul 22 2001 - 01:33:07 CEST