How to prevent db bloating

Question

I have a multiuser db that cotains a form for users to enter a custom query. This creates a table for their search results and presents the results to them in table view. Everytime the user runs a search, the table is deleted and recreated because their outpu columns may be different than what was previously there.

Well, the obvious is happening, the db is bloating. Is there another way to give the user their results without having to create a table and open it?

Answer

From: Shamil Salakhetdinov <shamil@marta.darts.spb.ru>
To: ACCESS-L <ACCESS-L@PEACH.EASE.LSOFT.COM>
Subject: Re: Bloating again
Date: 17 December 1998 2:19

John,

I think that something like the code in P.S. can be used to not bloat your front-end db.

HTH,
Shamil

Public Function ThisWillNotBloatCurrentDB()
  Dim strTempDBPath As String
  Dim strTempTblName As String
  Dim strSrcTblQryName As String
  Dim dbs As Database
  Dim strAppSql As String
  
  strTempDBPath = "c:\temp\~temp.mdb"
  strTempTblName = "~TempTable"
  strSrcTblQryName = "<your tablename here>"
  
  On Error Resume Next
  Kill strTempDBPath
  On Error GoTo 0
  
  DBEngine.CreateDatabase strTempDBPath, dbLangGeneral
  
  Set dbs = DBEngine(0).OpenDatabase(strTempDBPath)
  strAppSql = "select * into [" & strTempTblName & "] from [" & _
              strSrcTblQryName & "] in '' [;database=" & CurrentDb().Name & "]"
  dbs.Execute strAppSql
  
  ' SQL for qryTempDBResult stored in CurrentDB()
  'SELECT * FROM [~TempTable] IN '' [;database=c:\temp\~temp.mdb];
  DoCmd.OpenQuery "qryTempDBResult"
End Function