Help... Forgot to close variables....

[stickers]

Hi - this should be a quick one to sort out.

I have stupidly... forgotten to close two public variables - a connection object and a recordset object. I've been running the code a lot to test other things, and suddenly my database has grown by about 30mb in a day.... The recordset is a large one.

Has anyone got any ideas for sorting this mess out?

Cheers in advance.

 

[stickers]

OK - panic over - I created a new db and imported everything into it and that seems to have done the trick.

But has anyone got any tips for keeping size down - obviously I still have the same amount of data in there. Is it necessary to set all variables to nothing even if they are in private subs/functions? At the moment I don't set any of my string or integer variables to nothing - should I?

 

[Zathras]

When you use Set to create an object, you should set = Nothing when finished to free up the memory. (Stop "memory leaks&quot

Do not use Set = Nothing for variables. (The compiler will tell you that an object is required.)

Note that when you use Dim, you are defining a variable. When you use Dim xxx as Range (or any other object) you are also defining a variable. Viz., a pointer to the object. It has no meaning until you use Set to reserve memory for the object at which time the varible is updated to point to that part of memory. When the function or sub ends, the memory for the variable will be released, but if you don't didn't set = Nothing, the memory for the object will be lost until the program ends. (If you don't understand this last paragraph, ignore it. It's not important. You probably have plenty of memory.)

Your database problem is something else, however. I don't see how not using Set xx = Nothing could have caused that. I would guess that you have some code being executed that you didn't expect.

 

[stickers]

Zathras - thanks - but whatever is happening is going to be a real pain I think - since I copied this, I've run one event about 8 times (testing something) and the file size has gone from 4.6MB to 25.2MB.

Hmm. I need to do some searching around on this one I think.

 

[Nikita6003]

Hi Stickers

Does your code do a lot of writing to /deleting from the db?
We've found that any write action to an Access db using code will cause the filesize to increase dramatically.
We run iovernite import & calculation processes on a number of Access dbs, using Excel as front end, and we've solved the problem by compacting the db using code once processing is done. This means that the processes take longer, but we're no longer getting errors such as "the database has reached the maximum size allowed (it got to be about 2 Gigs after each run)
Plz make sure the db is closed before you run the following code - the db cannot be compacted unless the process has exclusive access - so you *need* to set all database objects to nothing & close the connection before running:

Public Function CompactDatabase(p_sSourceName As String, p_sTargetName As String) As Boolean
    Dim l_sResponse As String
    
    CompactDatabase = False
    On Error GoTo ErrorHandler
    
    If p_sSourceName = p_sTargetName Then
        'Build up compacted database with temporary name
        DBEngine.CompactDatabase p_sSourceName, p_sTargetName & "Tmp"
        'Delete original file
        Kill p_sTargetName
        'Rename temporary file to original name
        Name p_sTargetName & "Tmp" As p_sTargetName
    Else
        If FileExist(p_sTargetName) Then
            'File already exists. Ask user to overwrite file
            l_sResponse = MsgBox("File already exists... (" & p_sTargetName & ")" & vbLf & "Do you want to overwrite ?", vbYesNo, "Position model")
            If l_sResponse = vbYes Then
                'Kill existing archive
                Kill p_sTargetName
                'Compact database and copy file to new location
                DBEngine.CompactDatabase p_sSourceName, p_sTargetName
            End If
        Else
            'Compact database and copy file to new location
            DBEngine.CompactDatabase p_sSourceName, p_sTargetName
        End If
    End If
    
    CompactDatabase = True
    Exit Function
ErrorHandler:
    MsgBox Err.Description & " (" & Err.Number & ")"
    Exit Function
    Resume
End Function

To compact a database:

If Not CompactDatabase("Y:\Data\MSAccess\Test.mdb", "Y:\Data\MSAccess\Test.mdb") Then
    Err.Raise 99, , "ERROR: could not compact database "
    Exit Sub
End If

To compact *and* copy a database:

If Not CompactDatabase("Y:\Data\MSAccess\Test.mdb", "C:\Temp\CompactedTest.mdb") Then
    Err.Raise 99, , "ERROR: could not compact database"    Exit Sub
End If

HTH!



Cheers
Nikki
Look, mommy, I'm flying!

 

[stickers]

Nikki,

thanks for your post. That'll be the problem I think - this particular bit of code runs 4 delete and 4 append queries each time - and each time the number of records is fairly large. I've set it to compact on close, which solves the problem for now, but right now all I've got is test data in, so I'm worried about what will happen when I start using it in anger.

Do you know what the implications for all this are for a front end/back end system? If all the tables are in the back end, will this mdb get huge everytime?

Sarah

 

[Nikita6003]

Hi Sarah,

it's been our experience that using xl as frontend & access as backend will cause the mdb size to increase. The overnite processes generally clear out the database & import the new data files we receive. For one db we noticed it would grow from 48 MB to 1.2 GB overnight.

That's the reason we created the Function CompactDatabase; we use it to compact each database we process once all data has been deleted/imported. My best suggestion would be for you to do the same: you'll need to structure your code in this order:


1. open db connection

2. perform deletes/update

3. close db connection

4. call the CompactDatabase function.

this'll allow Excel to compact the database at the end of processing which should get rid of your problem

HTH


Cheers
Nikki
Look, mommy, I'm flying!