Access 2000 Database Growing 2

[zdas04]

I've got a small database that accepts customer data on a form, the salesman hits "calculate" and a VBA process updates a 1-record database with the key info, then the VBA runs two queries that do the calculations and update the table, finally the calculations are displayed on the original form. The key-entry database has a dummy primary key that is always "1" and the second field is the key to the prospect database. I was doing a make-table to shove the prospect key into a table, but I was concerned that everytime you delete a database Access fails to reclaim the space.

This all works fine (i.e., it is fast and the the data displayed on the form is correct), but after compacting the database it is 328k, after the first time you hit "calculate" it is 680k and every time you hit calculate after that it grows by 4K. If you compact the database after 100 calculations (and no new records added) it goes back to 328k.

Could the VBA be generating a new copy every time it is called? If so is there a TSR equivilent in VBA or Access?

 

[SgtJarrow]

Do the update queries create or use any temp tables? Access allocates data for anything as needed but does not relase the allocation until compacted....

****************************
When the human body encounters disease it raises its temperature making it uncomfortable not only for the body but also for the disease. So it global warming the Earth's way of saying we are not wanted?

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[zdas04]

No, I was VERY careful about that. The query that updates the dummy-key table gets deleted and re-created every time the calculate button is pressed. Could that be it? If so, is there a way to run the SQL code without creating a query?

 

[WayneRyan]

zdas04,

It is not unreasonable accept the fact that Access needs to
create some "scratch pad" space during its normal operations. Database "bloat" is a well-know problem with
Access.

It would be nice if it could reuse it, but it doesn't.

Have you checked out the Compact/Repair on exit. This
should be relatively painless.

Wayne

 

[zdas04]

Wayne,
What a great answer. The database is small enough that it compacts very quickly and recovers all the space. This might be a problem with a huge database, but it is perfect for this one. All the space was recovered. Thanks a lot.

I'd still like to see a way to pass a filter value to a query that was initiated from within VBA. We had that with REXX and SQL/DS 20 years ago. Looking through the threads in this forum dozens of them could be solved by allowing VBA to talk directly to a Query. Maybe it's time for an old idea to resurface.

David

 

[WayneRyan]

Hi David,

Stored queries can be invoked by VBA and can use form
controls as criteria.

It is also possible to create Query Definitions dynamically
and assign parameters through VBA.

Try a search on QDF here.

Wayne

 

[zdas04]

I've just spent almost 2 hours reading through the QDF stuff and I thought I had it till I loaded it into my Module and got a "Data Type Conversion Error".

The table field that I'm trying to go into is a Long Integer. The code looks like (there is error handling and stuff in the module that just clutters this up so I left it out):

Function Calc_Click(KeyIn As Long)

Dim dbs As Database
Dim rstSQL As dao.Recordset
Dim qdf As dao.QueryDef

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs("GrabKey0&quot

qdf!EnterKey = KeyIn

Set rstSQL = qdf.OpenRecordset("GrabKey0&quot

DoCmd.OpenQuery "GrabKey0"

End Function

The "GrabKey0" SQL code if pretty simple:

UPDATE HoldKey SET HoldKey.PrimaryKeyField = [EnterKey];

Any idea what amazingly stupid thing I might be doing here?

David

 

[WayneRyan]

David,

The SQL that you had will update every record in
the table!

Dim dbs As DAO.Database
dbs.Execute "UPDATE HoldKey " & _
            "SET HoldKey.PrimaryKeyField = " & EnterKey & ";"

Something like this seems to make more sense:

Dim dbs As DAO.Database
dbs.Execute "UPDATE HoldKey " & _
            "SET SomeField = '" & Me.SomeField "' " & _
            "Where PrimaryKeyField = " & Me.PrimaryKeyField ";"

Wayne

 

[zdas04]

Wayne,
"Every key in the table" is exactly one key. The HoldKey table has a dummy primary key with a value of 1 and then a data column called "PrimaryKeyField" that I'm updating. I update that one record and then join my other (very complex) queries to it to limit the update in the real table to the record displayed on the form. I know (now) that there should have been a way to just send the key from the form into the queries directly using the "qdf!Parameter" language, but I didn't know about that yesterday.

Your "dbs.Execute" syntax works perfectly. I just updated the database 50 times and the size did not change at all.

Thank you very much.

David

 

[nberryman]

Try faq705-2852

This neat bit of code compacts tha databse when it reaches a certain size



Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[zdas04]

Thanks Neil. That is a really good idea for a normal-sized database. This one is just tiny.

I don't envision this database to ever be bigger than a couple of meg now that it doesn't add 4k every time you hit enter. The "Compact on Close Always" seems to be just about the right level of complexity for this little-bitty puppy.

David