If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Front End Database File Size -- decompile
Jerry Whittle wrote:
geo threw you a curve. He said that it's an MDE file. Therefore it can't be decompiled. Right. When I need to change the code, I simply compile a new MDE file and I give this new MDE front end to the user. The file that is growing in size during use is the MDE file. All the information about decompiling the MDB file is great. In fact it allowed me to resolve several issues that I had with other databases that I am working on and it allowed me to reduce the size of the MDB file just like the articles above suggest. Thank you very much for the decompile information. It is extremely useful. It is just unrelated to my initial question. The purpose of my database is to collect information and, after some minimal processing, dump it into a back-end database. The front end is just a tiny (460KB) compiled MDE file. All the data is stored in the back end. The strange thing, to me, is that the size of the MDE file is growing during use. It is not being re-compiled and has no local tables. Yet, it can more than double in size after just a couple of uses. This MDE front end has a lot of VBA code but all of it is compiled and therefore static so that can not be growing. It has a single form that is the user interface and similarly should not be growing in size. It has links to eight tables in the back end - these are only links so they can not be growing either. So I suppose that the culprits must be the queries. I have six simple SELECT queries that do not really do anything but just display information. Finally, I have one append query and one update query - both of them targeting tables in the back end database. Would the append/update queries be caching the data locally as they run maybe? I also do have embedded SQL in the VBA so Pat Hartman's suggestion seems to be applicable as well. These are UPDATE and INSERT statements that are unavoidable. If I am stuck with having to periodically re-compress the front end, is it possible to initiate a "Compact and Repair Database" action from inside VBA code? I can easily monitor the file size and detect whenever a compression is needed from inside VBA. I am just not sure how to kick off the Compact and Repair Database action. I suppose I could always do a SendKeys with the %TDC keystrokes but that is like fixing furniture with duck-tape… George www.inspiredbrew.com -- Message posted via http://www.accessmonster.com |
#12
|
|||
|
|||
Front End Database File Size -- decompile
thanks, Jerry -- I had to read the inital post again because
I missed those 3 little letters... Warm Regards, Crystal Microsoft Access MVP 2006 * Have an awesome day remote programming and training strive4peace2006 at yahoo.com * Jerry Whittle wrote: geo threw you a curve. He said that it's an MDE file. Therefore it can't be decompiled. |
#13
|
|||
|
|||
Front End Database File Size -- compact, temporary queries
Hi geo,
As Pat suggested in her discussion about decompiling, you may do something that I do -- construct lots of SQL in code. For grins, run find out what the db has for stored queries... this may surprise you... '~~~~~~~~~~~ Sub listQuerySQL() Dim qdf As dao.QueryDef, db As dao.Database Dim i As Integer Set db = CurrentDb 'or whatever i = 0 For Each qdf In db.QueryDefs i = i + 1 If MsgBox(qdf.SQL, vbOKCancel, _ i & " " & qdf.Name) = vbCancel Then Exit Sub 'space in debug widnow is limited ' -- not all will show... look at counter (i) 'to see what you are missing Debug.Print "--- " & i & " -- " & qdf.Name & " ---" Debug.Print qdf.SQL Next qdf Set qdf = Nothing Set db = Nothing End Sub '~~~~~~~~~~~~~~ if you do have a lot of these temporary queries, you may wish to set up a routine to delete them. They start with ~ I don't know how this will work for mde files... but here is some code that works for mdb files that you can perhaps adapt to compile your db '~~~~~~~~~~~~~~~~~ 'NEEDS REference To Microsoft DAO Library 'make sure DAO is as high up on the order as it will go '------------------------------------------ CompactDB Sub CompactDB(pDB As String) On Error GoTo CompactDB_error ' make sure db is there If Len(Dir(pDB)) = 0 Then MsgBox pDB & " does not exist", , "Missing File" Exit Sub End If Dim mTempDB As String 'always compact/repair on a local drive mTempDB = "c:\tmp.mdb" 'erase temporary file If Len(Dir(mTempDB)) 0 Then DeleteFile mTempDB End If DBEngine.CompactDatabase pDB, mTempDB ' make sure compacted db is there If Len(Dir(mTempDB)) 0 Then ' delete original DeleteFile pDB ' rename compacted db back to original name Name mTempDB As pDB WaitForFile pDB End If CompactDB_exit: Exit Sub CompactDB_error: MsgBox Err.Description, , "ERROR " & Err.Number & " CompactDB" 'press F8 to step through code and fix problem 'comment out next two lines after code is debugged Stop Resume GoTo CompactDB_exit End Sub '------------------------------------------ WaitForFile Sub WaitForFile(pFile As String) On Error GoTo WaitForFile_error SysCmd acSysCmdSetStatus, "Waiting for " & pFile & " ..." Do While Len(Dir(pFile)) = 0 ' wait for file to be there DoEvents Loop WaitForFile_exit: SysCmd acSysCmdClearStatus Exit Sub WaitForFile_error: MsgBox Err.Description _ , , "ERROR " & Err.Number & " WaitForFile" 'press F8 to step through code and fix problem 'comment out next two lines after code is debugged Stop Resume GoTo WaitForFile_exit End Sub '------------------------------------------ DeleteFile Sub DeleteFile(pFile As String) On Error GoTo DeleteFile_error Kill pFile SysCmd acSysCmdSetStatus, _ "Waiting for " & pFile & " to get erased ..." Do While Len(Dir(pFile)) 0 DoEvents Loop DeleteFile_exit: SysCmd acSysCmdClearStatus Exit Sub DeleteFile_error: MsgBox Err.Description _ , , "ERROR " & Err.Number & " DeleteFile" 'press F8 to step through code and fix problem 'comment out next two lines after code is debugged Stop Resume GoTo DeleteFile_exit End Sub '~~~~~~~~~~~~~~` Warm Regards, Crystal Microsoft Access MVP 2006 * Have an awesome day remote programming and training strive4peace2006 at yahoo.com * geo via AccessMonster.com wrote: Jerry Whittle wrote: geo threw you a curve. He said that it's an MDE file. Therefore it can't be decompiled. Right. When I need to change the code, I simply compile a new MDE file and I give this new MDE front end to the user. The file that is growing in size during use is the MDE file. All the information about decompiling the MDB file is great. In fact it allowed me to resolve several issues that I had with other databases that I am working on and it allowed me to reduce the size of the MDB file just like the articles above suggest. Thank you very much for the decompile information. It is extremely useful. It is just unrelated to my initial question. The purpose of my database is to collect information and, after some minimal processing, dump it into a back-end database. The front end is just a tiny (460KB) compiled MDE file. All the data is stored in the back end. The strange thing, to me, is that the size of the MDE file is growing during use. It is not being re-compiled and has no local tables. Yet, it can more than double in size after just a couple of uses. This MDE front end has a lot of VBA code but all of it is compiled and therefore static so that can not be growing. It has a single form that is the user interface and similarly should not be growing in size. It has links to eight tables in the back end - these are only links so they can not be growing either. So I suppose that the culprits must be the queries. I have six simple SELECT queries that do not really do anything but just display information. Finally, I have one append query and one update query - both of them targeting tables in the back end database. Would the append/update queries be caching the data locally as they run maybe? I also do have embedded SQL in the VBA so Pat Hartman's suggestion seems to be applicable as well. These are UPDATE and INSERT statements that are unavoidable. If I am stuck with having to periodically re-compress the front end, is it possible to initiate a "Compact and Repair Database" action from inside VBA code? I can easily monitor the file size and detect whenever a compression is needed from inside VBA. I am just not sure how to kick off the Compact and Repair Database action. I suppose I could always do a SendKeys with the %TDC keystrokes but that is like fixing furniture with duck-tape… George www.inspiredbrew.com |
#14
|
|||
|
|||
Front End Database File Size
thanks, Jerry
yes, I didn't mention that, but did... compact that is, after decomile. This particular db is set to compact on close and I check file size after it closes. Warm Regards, Crystal Microsoft Access MVP 2006 * Have an awesome day remote programming and training strive4peace2006 at yahoo.com * Jerry Whittle wrote: Not too weird at all. When you decompiled the database, all the code got translated into text. If you recompiled the text still stayed around. Did you recompact the database after compiling? That's when the text is removed and then your database should be smaller or the same size as when you started. |
#15
|
|||
|
|||
Front End Database File Size -- compact, temporary queries
Great post. This has really helped me to reduce the size of my Dbs.
However, when I run the following code to delete the queries, it does not delete them all in one pass and needs multiple passes to delete all the unwanted queries. The database string I pass into the sub is external to the database from which it is run. Any help to solve this is much appreciated. Sub delete_sys_query(pDB As String) Dim qdf As DAO.QueryDef, db As DAO.Database Dim i As Integer Set db = DBEngine.Workspaces(0).OpenDatabase(pDB) i = 0 For Each qdf In db.QueryDefs If InStr(qdf.Name, "~") 0 Then i = i + 1 'Debug.Print "--- " & i & " -- " & qdf.Name & " ---" db.QueryDefs.Delete qdf.Name End If Next qdf Set qdf = Nothing Set db = Nothing End Sub ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ "strive4peace" "strive4peace2006 at yaho" wrote: Hi geo, As Pat suggested in her discussion about decompiling, you may do something that I do -- construct lots of SQL in code. For grins, run find out what the db has for stored queries... this may surprise you... '~~~~~~~~~~~ Sub listQuerySQL() Dim qdf As dao.QueryDef, db As dao.Database Dim i As Integer Set db = CurrentDb 'or whatever i = 0 For Each qdf In db.QueryDefs i = i + 1 If MsgBox(qdf.SQL, vbOKCancel, _ i & " " & qdf.Name) = vbCancel Then Exit Sub 'space in debug widnow is limited ' -- not all will show... look at counter (i) 'to see what you are missing Debug.Print "--- " & i & " -- " & qdf.Name & " ---" Debug.Print qdf.SQL Next qdf Set qdf = Nothing Set db = Nothing End Sub '~~~~~~~~~~~~~~ if you do have a lot of these temporary queries, you may wish to set up a routine to delete them. They start with ~ I don't know how this will work for mde files... but here is some code that works for mdb files that you can perhaps adapt to compile your db '~~~~~~~~~~~~~~~~~ 'NEEDS REference To Microsoft DAO Library 'make sure DAO is as high up on the order as it will go '------------------------------------------ CompactDB Sub CompactDB(pDB As String) On Error GoTo CompactDB_error ' make sure db is there If Len(Dir(pDB)) = 0 Then MsgBox pDB & " does not exist", , "Missing File" Exit Sub End If Dim mTempDB As String 'always compact/repair on a local drive mTempDB = "c:\tmp.mdb" 'erase temporary file If Len(Dir(mTempDB)) 0 Then DeleteFile mTempDB End If DBEngine.CompactDatabase pDB, mTempDB ' make sure compacted db is there If Len(Dir(mTempDB)) 0 Then ' delete original DeleteFile pDB ' rename compacted db back to original name Name mTempDB As pDB WaitForFile pDB End If CompactDB_exit: Exit Sub CompactDB_error: MsgBox Err.Description, , "ERROR " & Err.Number & " CompactDB" 'press F8 to step through code and fix problem 'comment out next two lines after code is debugged Stop Resume GoTo CompactDB_exit End Sub '------------------------------------------ WaitForFile Sub WaitForFile(pFile As String) On Error GoTo WaitForFile_error SysCmd acSysCmdSetStatus, "Waiting for " & pFile & " ..." Do While Len(Dir(pFile)) = 0 ' wait for file to be there DoEvents Loop WaitForFile_exit: SysCmd acSysCmdClearStatus Exit Sub WaitForFile_error: MsgBox Err.Description _ , , "ERROR " & Err.Number & " WaitForFile" 'press F8 to step through code and fix problem 'comment out next two lines after code is debugged Stop Resume GoTo WaitForFile_exit End Sub '------------------------------------------ DeleteFile Sub DeleteFile(pFile As String) On Error GoTo DeleteFile_error Kill pFile SysCmd acSysCmdSetStatus, _ "Waiting for " & pFile & " to get erased ..." Do While Len(Dir(pFile)) 0 DoEvents Loop DeleteFile_exit: SysCmd acSysCmdClearStatus Exit Sub DeleteFile_error: MsgBox Err.Description _ , , "ERROR " & Err.Number & " DeleteFile" 'press F8 to step through code and fix problem 'comment out next two lines after code is debugged Stop Resume GoTo DeleteFile_exit End Sub '~~~~~~~~~~~~~~` Warm Regards, Crystal Microsoft Access MVP 2006 * Have an awesome day remote programming and training strive4peace2006 at yahoo.com * geo via AccessMonster.com wrote: Jerry Whittle wrote: geo threw you a curve. He said that it's an MDE file. Therefore it can't be decompiled. Right. When I need to change the code, I simply compile a new MDE file and I give this new MDE front end to the user. The file that is growing in size during use is the MDE file. All the information about decompiling the MDB file is great. In fact it allowed me to resolve several issues that I had with other databases that I am working on and it allowed me to reduce the size of the MDB file just like the articles above suggest. Thank you very much for the decompile information. It is extremely useful. It is just unrelated to my initial question. The purpose of my database is to collect information and, after some minimal processing, dump it into a back-end database. The front end is just a tiny (460KB) compiled MDE file. All the data is stored in the back end. The strange thing, to me, is that the size of the MDE file is growing during use. It is not being re-compiled and has no local tables. Yet, it can more than double in size after just a couple of uses. This MDE front end has a lot of VBA code but all of it is compiled and therefore static so that can not be growing. It has a single form that is the user interface and similarly should not be growing in size. It has links to eight tables in the back end - these are only links so they can not be growing either. So I suppose that the culprits must be the queries. I have six simple SELECT queries that do not really do anything but just display information. Finally, I have one append query and one update query - both of them targeting tables in the back end database. Would the append/update queries be caching the data locally as they run maybe? I also do have embedded SQL in the VBA so Pat Hartman's suggestion seems to be applicable as well. These are UPDATE and INSERT statements that are unavoidable. If I am stuck with having to periodically re-compress the front end, is it possible to initiate a "Compact and Repair Database" action from inside VBA code? I can easily monitor the file size and detect whenever a compression is needed from inside VBA. I am just not sure how to kick off the Compact and Repair Database action. I suppose I could always do a SendKeys with the %TDC keystrokes but that is like fixing furniture with duck-tape… George www.inspiredbrew.com |
#16
|
|||
|
|||
Front End Database File Size -- compact, temporary queries
Rather than:
For Each qdf In db.QueryDefs If InStr(qdf.Name, "~") 0 Then i = i + 1 'Debug.Print "--- " & i & " -- " & qdf.Name & " ---" db.QueryDefs.Delete qdf.Name End If Next qdf which works from the front of the collection to the end, you need to start at the end of the collection, and work forward: For intLoop = (db.QueryDefs.Count - 1) To 0 Step -1 If InStr(db.QueryDefs(intLoop).Name, "~") 0 Then i = i + 1 'Debug.Print "--- " & i & " -- " & db.QueryDefs(intLoop).Name & " ---" db.QueryDefs.Delete db.QueryDefs(intLoop).Name End If Next intLoop What happens when you work from the front to the back is once you delete a specific query, the point automatically moves to the next query. However, you then have your "Next qdf", which then moves to the query after that so that you actually skip every other query. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "ianc" wrote in message ... Great post. This has really helped me to reduce the size of my Dbs. However, when I run the following code to delete the queries, it does not delete them all in one pass and needs multiple passes to delete all the unwanted queries. The database string I pass into the sub is external to the database from which it is run. Any help to solve this is much appreciated. Sub delete_sys_query(pDB As String) Dim qdf As DAO.QueryDef, db As DAO.Database Dim i As Integer Set db = DBEngine.Workspaces(0).OpenDatabase(pDB) i = 0 For Each qdf In db.QueryDefs If InStr(qdf.Name, "~") 0 Then i = i + 1 'Debug.Print "--- " & i & " -- " & qdf.Name & " ---" db.QueryDefs.Delete qdf.Name End If Next qdf Set qdf = Nothing Set db = Nothing End Sub ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ "strive4peace" "strive4peace2006 at yaho" wrote: Hi geo, As Pat suggested in her discussion about decompiling, you may do something that I do -- construct lots of SQL in code. For grins, run find out what the db has for stored queries... this may surprise you... '~~~~~~~~~~~ Sub listQuerySQL() Dim qdf As dao.QueryDef, db As dao.Database Dim i As Integer Set db = CurrentDb 'or whatever i = 0 For Each qdf In db.QueryDefs i = i + 1 If MsgBox(qdf.SQL, vbOKCancel, _ i & " " & qdf.Name) = vbCancel Then Exit Sub 'space in debug widnow is limited ' -- not all will show... look at counter (i) 'to see what you are missing Debug.Print "--- " & i & " -- " & qdf.Name & " ---" Debug.Print qdf.SQL Next qdf Set qdf = Nothing Set db = Nothing End Sub '~~~~~~~~~~~~~~ if you do have a lot of these temporary queries, you may wish to set up a routine to delete them. They start with ~ I don't know how this will work for mde files... but here is some code that works for mdb files that you can perhaps adapt to compile your db '~~~~~~~~~~~~~~~~~ 'NEEDS REference To Microsoft DAO Library 'make sure DAO is as high up on the order as it will go '------------------------------------------ CompactDB Sub CompactDB(pDB As String) On Error GoTo CompactDB_error ' make sure db is there If Len(Dir(pDB)) = 0 Then MsgBox pDB & " does not exist", , "Missing File" Exit Sub End If Dim mTempDB As String 'always compact/repair on a local drive mTempDB = "c:\tmp.mdb" 'erase temporary file If Len(Dir(mTempDB)) 0 Then DeleteFile mTempDB End If DBEngine.CompactDatabase pDB, mTempDB ' make sure compacted db is there If Len(Dir(mTempDB)) 0 Then ' delete original DeleteFile pDB ' rename compacted db back to original name Name mTempDB As pDB WaitForFile pDB End If CompactDB_exit: Exit Sub CompactDB_error: MsgBox Err.Description, , "ERROR " & Err.Number & " CompactDB" 'press F8 to step through code and fix problem 'comment out next two lines after code is debugged Stop Resume GoTo CompactDB_exit End Sub '------------------------------------------ WaitForFile Sub WaitForFile(pFile As String) On Error GoTo WaitForFile_error SysCmd acSysCmdSetStatus, "Waiting for " & pFile & " ..." Do While Len(Dir(pFile)) = 0 ' wait for file to be there DoEvents Loop WaitForFile_exit: SysCmd acSysCmdClearStatus Exit Sub WaitForFile_error: MsgBox Err.Description _ , , "ERROR " & Err.Number & " WaitForFile" 'press F8 to step through code and fix problem 'comment out next two lines after code is debugged Stop Resume GoTo WaitForFile_exit End Sub '------------------------------------------ DeleteFile Sub DeleteFile(pFile As String) On Error GoTo DeleteFile_error Kill pFile SysCmd acSysCmdSetStatus, _ "Waiting for " & pFile & " to get erased ..." Do While Len(Dir(pFile)) 0 DoEvents Loop DeleteFile_exit: SysCmd acSysCmdClearStatus Exit Sub DeleteFile_error: MsgBox Err.Description _ , , "ERROR " & Err.Number & " DeleteFile" 'press F8 to step through code and fix problem 'comment out next two lines after code is debugged Stop Resume GoTo DeleteFile_exit End Sub '~~~~~~~~~~~~~~` Warm Regards, Crystal Microsoft Access MVP 2006 * Have an awesome day remote programming and training strive4peace2006 at yahoo.com * geo via AccessMonster.com wrote: Jerry Whittle wrote: geo threw you a curve. He said that it's an MDE file. Therefore it can't be decompiled. Right. When I need to change the code, I simply compile a new MDE file and I give this new MDE front end to the user. The file that is growing in size during use is the MDE file. All the information about decompiling the MDB file is great. In fact it allowed me to resolve several issues that I had with other databases that I am working on and it allowed me to reduce the size of the MDB file just like the articles above suggest. Thank you very much for the decompile information. It is extremely useful. It is just unrelated to my initial question. The purpose of my database is to collect information and, after some minimal processing, dump it into a back-end database. The front end is just a tiny (460KB) compiled MDE file. All the data is stored in the back end. The strange thing, to me, is that the size of the MDE file is growing during use. It is not being re-compiled and has no local tables. Yet, it can more than double in size after just a couple of uses. This MDE front end has a lot of VBA code but all of it is compiled and therefore static so that can not be growing. It has a single form that is the user interface and similarly should not be growing in size. It has links to eight tables in the back end - these are only links so they can not be growing either. So I suppose that the culprits must be the queries. I have six simple SELECT queries that do not really do anything but just display information. Finally, I have one append query and one update query - both of them targeting tables in the back end database. Would the append/update queries be caching the data locally as they run maybe? I also do have embedded SQL in the VBA so Pat Hartman's suggestion seems to be applicable as well. These are UPDATE and INSERT statements that are unavoidable. If I am stuck with having to periodically re-compress the front end, is it possible to initiate a "Compact and Repair Database" action from inside VBA code? I can easily monitor the file size and detect whenever a compression is needed from inside VBA. I am just not sure how to kick off the Compact and Repair Database action. I suppose I could always do a SendKeys with the %TDC keystrokes but that is like fixing furniture with duck-tape. George www.inspiredbrew.com |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help! Database mysteriously deleting | Mark | General Discussion | 10 | October 20th, 2009 04:03 PM |
Cannot Reinstall Office 2003 | aronius | Setup, Installing & Configuration | 6 | February 10th, 2007 09:26 PM |
MSI Error on administration deployment | Scrappo | Setup, Installing & Configuration | 7 | March 7th, 2006 04:52 PM |
.jpg .gif .bmp file associations gone, upgrade from Office XP to 2 | fbartrom | Setup, Installing & Configuration | 13 | February 25th, 2005 01:33 PM |
Product Key for Office XP | P.G.Indiana | Setup, Installing & Configuration | 1 | June 7th, 2004 03:22 AM |