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?
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
Copyright © 1999-2008 by Shamil Salakhetdinov.