VBA Code

[Guest]

i have a procedure:
Public Sub NegotSupDataTransfer()
' to delete old data from table, update with new data and open pivot table

DoCmd.Echo False, "Preparing data, please be patient."
DoCmd.OpenTable "NegotSummaryTable"
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdDelete
DoCmd.OpenQuery "SummaryCaseQualityInfo2"
DoCmd.OpenQuery "SummaryPhoneQualityInfo2"
DoCmd.OpenQuery "SummaryWrittenQualityInfo2"
DoCmd.Close acTable, "NegotSummaryTable", acSaveYes

End Sub

everytime it runs access locks up and i have to close out. i'm fairly new to
VBA...any suggestions?

 

[tina]

well, you don't say what line of code causes the system to lock up. suggest
you try stepping through the code to find out. my only suggestion beyond
that is that you don't need to open a table to delete its' records. just run
a Delete query, as

DoCmd.SetWarnings False
CurrentDb.Execute "DELETE * FROM NegotSummaryTable", _
dbFailOnError
DoCmd.SetWarnings True

DoCmd.OpenQuery "SummaryCaseQualityInfo2"
DoCmd.OpenQuery "SummaryPhoneQualityInfo2"
DoCmd.OpenQuery "SummaryWrittenQualityInfo2"

also, if the three above queries are Append queries, then i assume you're
appending data from other tables into NegotSummaryTable. suggest you
consider combining those three queries into a fourth query instead, and
running your pivot table from that. in that case you wouldn't need the extra
table object at all.

hth

 

[Guest]

when i stepped through the code, everything works fine until it's complete.
then the screen freezes and the only thing it will let me do is close out. I
even tried the code you suggested and it still locks up.

i am working on making the 3 queries into one and we will see if that works.
thank you for your time and suggestions.

 

[Steve Schapel]

Tina,

Just a small comment... You don't need to manipulate the SetWarnings if
using the .Execute method. This really only applies to OpenQuery or
RunSQL methods.

 

[tina]

thanks for tweaking, Steve if i didn't know that already (stupid
attack?), i should have! <g>