HELP! My database is exploding!!

[dirkg]

I have this rather big problem: I have a database in which some procedures run which use a lot dynamic queries (i.e. queries made from score) When I run these procedure, my database grows from 700K to more than 200 MB!! And this is not because there are more data in it because all the data are stored in linked databases and after compacting, the database gets back its normal size of 700K.<br>I know that this probably comes from the dynamic queries since somebody told me that Access doesn't &quot;re-use&quot; that space. I can't make these queries static either because the program runs about thousand queries and they all change every time.<br><br>Is there somebody who can help me with this problem? I was thinking about compacting the database from within the code but then I have to close the database since I'm still working in Access 95 (yes I know, that one should belong in a museum but it doesn't sem to be so bad after all if I see the comments of dougp on Access 2000).<br><br>All tips would be realy appreciated!Thanks a lot in advance!!<br><br>Greetings,<br><br>Dirk<br><br><A HREF="mailto:dirk.news@yucom.be">dirk.news@yucom.be</A><br>

 

[DougP]

Access '97 is better but hard to find now.<br>Lets see some of your dynamic query code.<br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[JimHorton]

Every time you change the sql of a querydef, you save that instnace of the single querydef as a 'new' hidden query.&nbsp;&nbsp;They are never deleted until compaction.&nbsp;&nbsp;The answer is to use the CreateQueryDef() method and use a <i>blank</i> name for the name argument. Then it does not become permanent.&nbsp;&nbsp;<br>--Jim

 

[dirkg]

most of the queries look like this:<br>strSQLEKBE = &quot;SELECT * FROM [&quot; & strPrefix & &quot;EKBE] WHERE (EBELN = &quot; & dblEBELN & &quot; and EBELP = &quot; & lngEBELP & &quot; AND CALC &gt; 0 AND BWART IN (SELECT BWART FROM sysBWART)) ORDER BY BUDAT;&quot;<br><br>strSQLEKET = &quot;SELECT * FROM [&quot; & strPrefix & &quot;EKET] WHERE (EBELN = &quot; & dblEBELN & &quot; and EBELP = &quot; & lngEBELP & &quot ORDER BY EINDT, ETENR;&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set dbs = CurrentDb<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rstEKBE = dbs.OpenRecordset(strSQLEKBE)<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rstEKET = dbs.OpenRecordset(strSQLEKET)<br><br>Maybe the answer can lie indeed in the tip of Jim since there is one querydef from which I change the SQL-statement over and over again. I'll try that tip out and let you know the result.<br><br>Greetings,<br><br>Dirk