Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am performing a large number of insert operations using VBA - Access. the VBA code is in a file "control.accdb" performing operations on "data.accdb" Few tables in the process are created from the VBA , few remain as such.

After finishing Phase 1 of the process , I do a Compact And Repair either manually or from DBEngine.Compact VBA code. The second phase (same code - next iteration) becomes very very slow.

Here is the Insert Code :

INSERT INTO ExpectedResult (DLID, NumRows, Total, SubjectBlock, NextSubjectBlockSeq ) 
SELECT INVDL.DLID, 1 AS Expr1, INVDL.Amount,    INVDL.SubjectBlock, INVDL.SubjectBlockSeq+1 AS Expr2
FROM INVDL where SubjectBlock > 0;"
cdb.Execute (SQL)
TargetNumRows = 2
Do While TargetNumRows < MaxSubjectBlockSeq + 1
  Set qdf = cdb.QueryDefs("pq_appendToExpectedResult")
  qdf!TargetNumRows = TargetNumRows  '' parameter value
  TargetNumRows = TargetNumRows + 1
  Set qdf = Nothing

where pq_appendToExpectedResult is:

PARAMETERS TargetNumRows Long;
INSERT INTO ExpectedResult ( DLID, NumRows, Total, SubjectBlock, NextSubjectBlockSeq )
SELECT INVDL.DLID+1-[TargetNumRows], [TargetNumRows] AS Expr1, [ExpectedResult].[Total]+    [INVDL].[Amount] AS NewTotal, INVDL.SubjectBlock, [INVDL].[SubjectBlockSeq]+1     AS Expr2
FROM INVDL INNER JOIN ExpectedResult ON (INVDL.SubjectBlock =     ExpectedResult.SubjectBlock) AND (INVDL.SubjectBlockSeq =     ExpectedResult.NextSubjectBlockSeq)
WHERE (((INVDL.SubjectBlockSeq)>=[TargetNumRows]) AND (ExpectedResult.NumRows=    [TargetNumRows]-1));

To the best of my knowledge , all indexes in INVDL are untouched. I drop the table ExpectedResult and recreate it with indexes after each Phase (iteration). Other tables - dont think will make any difference.

share|improve this question
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.