using a query to compact and repair

[Nick Monsour]

Good morning all,

Is it possible to use a query to preform the Compact and Repair
command?

Thank you,
Nick

 

[John Vinson]

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]

 

[Nick Monsour]

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

 

[John Vinson]

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]

 

[Nick Monsour]

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

 

[John Vinson]

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

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]

 

[Nick Monsour]

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

 

[John Vinson]

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

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]

 

[Nick Monsour]

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)

 

[Nick Monsour]

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)

 

[John Vinson]

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]