 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / July 2006using a query to compact and repair
Good morning all, Is it possible to use a query to preform the Compact and Repair command? Thank you, Nick >Is it possible to use a query to preform the Compact and Repair >command? No. VBA code can do it though. John W. Vinson[MVP] Unfortunately I don't know anything about VBA coding as of yet. It sounds like this would be hard. But if I could get it done, could it be put as a step in a macro? Thank you, Nick >Unfortunately I don't know anything about VBA coding as of yet. It >sounds like this would be hard. But if I could get it done, could it be >put as a step in a macro? Unfortunately, no. The problem is that a database cannot be open when it's being compacted - you need to use VBA code which actually causes the database to be closed, compacted, and then reopened. Why not just use Compact On Close? Just what are you trying to accomplish by doing this maintenance operation in a query or macro? John W. Vinson[MVP] I am in a new position (job) and the pers9on that had this job before has left me with some automated stuff in Access 97, Xp, and 2003. THe particular update macro I am working with blows up before it can finish. If I compact it before it runs it will still blow up due to size issues. I don't know whyand I only have limited experience with Access to this point. So I figured that if I could Compact and Repair during it might finish. It just gets too big. Nick >I am in a new position (job) and the pers9on that had this job before >has left me with some automated stuff in Access 97, Xp, and 2003. THe [quoted text clipped - 5 lines] > >Nick How big is the database when you start? Is it close to 2 GByte already? I STRONGLY suspect that compacting will merely delay the problem for a few seconds; that there is an error in the update... "macro"? perhaps an update Query?... that's causing some sort of unlimited expansion. Could you post the details of the macro, and (if it's calling an update query) the SQL view of the query? Let's fix the problem rather than struggling with a probably non-helpful getaround! John W. Vinson[MVP] Hi John, Sorry it took me so long to write back. I can start the process from the day before and the size is 1,313,916 KB. Directly after doing a Compact and Repair the size is 1,275,204 KB. Then I run an update macro that used to run without issue (not created by me) and it throws an error of 'invalid argument' and the database size has grown to 2,095,108 KB. I then compact and repair again and the size is 942,620 KB. The macro dies after a delete query and during an append query at step 8 of 14 steps. The process goes as follows 1 make table qry 2 update qry 3 delete qry 4 append qry 5 delete qry 6 append qry 7 delete qry 8 append qry ****dies here**** 9 make table qry 10 update qry 11 update qry 12 update qry 13 update qry 14 update qry Please let me know if you need more info and thank you for the help! Nick >Hi John, > [quoted text clipped - 23 lines] >Please let me know if you need more info and thank you for the help! >Nick Well, YOU can see the 8th query. I cannot. All I can say is "fix the error in the eighth query". If you would like help doing so, please open it in SQL view and post the SQL text here. John W. Vinson[MVP] I get an error when I try to view the SQL code: The SQL statement could not be executed because it contains ambiguous outer loins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement. I got this out of the documenter: Properties DateCreated: 3/16/2006 10:46:39 AM DOL: Long binary data GUID: {guid {6DEFE900-F5FE- LastUpdated: 7/12/2006 10:49:49 AM 472D-967F- MaxRecords: 0 ODBCTimeout: 0 Orientation: Left-to-Right RecordLocks: Edited Record RecordsAffected: 0 ReturnsRecords: True Type: 80 Updatable: True UseTransaction: True SQL SELECT dbo_tblAR_Base.CUS_NUM_AR, dbo_tblAR_Base.NAME_CM, dbo_tblAR_Base.APPLY_TO_AR, dbo_tblAR_Base.MEDLINE_AR, dbo_tblAR_Base.INS_CO_AR, [CUS_NUM_AR] & CLng([APPLY_TO_AR]) & CLng([MEDLINE_AR]) & [INS_CO_AR] AS MatchLnIns, dbo_tblAR_Base.DOC_DATE_AR, dbo_tblAR_Base.TOTALAMOUNT, dbo_tblAR_Base.DESC_CODE_AR, dbo_tblAR_Base.REC_SUBTYPE_AR, dbo_tblAR_Base.ARTYPEID, dbo_tblAR_Base.EffctDt, dbo_tblAR_Base.GroupID INTO tblAR1st FROM tblERN_LastDenial INNER JOIN dbo_tblAR_Base ON (tblERN_LastDenial.CUSTNUM_ER = dbo_tblAR_Base.CUS_NUM_AR) AND (tblERN_LastDenial.InvNum = dbo_tblAR_Base.APPLY_TO_AR) AND (tblERN_LastDenial.LineNum = dbo_tblAR_Base.MEDLINE_AR) AND (tblERN_LastDenial.INSNUM_ER = dbo_tblAR_Base.INS_CO_AR) >I get an error when I try to view the SQL code: >The SQL statement could not be executed because it contains ambiguous >outer loins. To force one of the joins to be performed first, create a >separate query that performs the first join and then include that query >in your SQL statement. I'm sorry, but this is much too involved to reliably diagnose on the newsgroups. This query will have to either be deleted and (correctly) recreated, or someone who understands the tables and the data involved (the original developer??) will have to fix it. I wish I could be more help, but anything I could suggest would be guessing in the dark and might do more harm than good. John W. Vinson[MVP] I get an error when I try to view the SQL code: The SQL statement could not be executed because it contains ambiguous outer loins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement. I got this out of the documenter: Properties DateCreated: 3/16/2006 10:46:39 AM DOL: Long binary data GUID: {guid {6DEFE900-F5FE- LastUpdated: 7/12/2006 10:49:49 AM 472D-967F- MaxRecords: 0 ODBCTimeout: 0 Orientation: Left-to-Right RecordLocks: Edited Record RecordsAffected: 0 ReturnsRecords: True Type: 80 Updatable: True UseTransaction: True SQL SELECT dbo_tblAR_Base.CUS_NUM_AR, dbo_tblAR_Base.NAME_CM, dbo_tblAR_Base.APPLY_TO_AR, dbo_tblAR_Base.MEDLINE_AR, dbo_tblAR_Base.INS_CO_AR, [CUS_NUM_AR] & CLng([APPLY_TO_AR]) & CLng([MEDLINE_AR]) & [INS_CO_AR] AS MatchLnIns, dbo_tblAR_Base.DOC_DATE_AR, dbo_tblAR_Base.TOTALAMOUNT, dbo_tblAR_Base.DESC_CODE_AR, dbo_tblAR_Base.REC_SUBTYPE_AR, dbo_tblAR_Base.ARTYPEID, dbo_tblAR_Base.EffctDt, dbo_tblAR_Base.GroupID INTO tblAR1st FROM tblERN_LastDenial INNER JOIN dbo_tblAR_Base ON (tblERN_LastDenial.CUSTNUM_ER = dbo_tblAR_Base.CUS_NUM_AR) AND (tblERN_LastDenial.InvNum = dbo_tblAR_Base.APPLY_TO_AR) AND (tblERN_LastDenial.LineNum = dbo_tblAR_Base.MEDLINE_AR) AND (tblERN_LastDenial.INSNUM_ER = dbo_tblAR_Base.INS_CO_AR) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.