Increasing database

[olle]

I´m using this code to get data (fill the recordset) from a database (Access97)

set mydb = OpenDatabase("database.mdb")
set myQuery = mydb.CreateQueryDef("aqry", "SELECT * FROM ATABLE")
set qryRS = mydb.OpenRecordset("aqry", dbOpenDynaset)



this makes the database to grow from 1 MB to 16 MB, why????


olle

[Andrew_Fryer]

Hello,

Access uses loads of temporary objects, which all increase the space taken.

Use Tools->Database Utilities->Compact Database in Access to fix it or use a code method.

[MKSa]

I think there is a problem with the way you use the OpenRecordset method. For QueryDef, Recordset, and TableDef objects the syntax is:

set recordset = object.OpenRecordset (type, options, lockedits)



So your third line should be:

set qryRS = myQuery.OpenRecordset(dbOpenDynaset)



Please inform if this solve the problem.

[Jenda]

Try using a different option to OpenRecordset if you can. I believe dbOpenDynaset is one of the most expensive.

Jenda

[olle]

I´ve tried your suggestion but it doesn´t make any difference the database still grows rapidly(from 1 MB to 22-32 MB)
(It seems as if every recordset I open get stored in the database, is it so or am I wrong??)

[MKSa]

The Syntax for using the OpenRecordset that I gave is the syntax given in the documentation. I've tried your code on several databases and different tables but was not able to notice any big changes in the databases size. So can you give more information like references, dimensions, more code...

[olle]

I´ve found the problem, if you in a loop use mydb.CreateQueryDef (and delete the QueryDef) the database grows rapidly. The solution seems to be to use

set qryRS = mydb.OpenRecordset(sqlstatement, dbOpenSnapshot)



thank you for the help