Macro limit? creating overflow

[Guest]

Anyone else had this problem?

I have to run a macro about 100000 times(through a module that loops), that
appends a query to a table(only 4 fields per entry) Works fine for about
34000 times then pops up with "OverFlow" error box.
Is there a limit on the number of appends you can do to a table or is it a
macro limit?
Im using access 2002, maybe its just too much for the memory and possible
access 2007 can handle it?
Just interested if anyone knows why it does that and if it can be fixed.

 

[Guest]

There are several memory limits like that, normally
tied to the number of locks you can maintain on a table.
Unlocking is done as a background process, so you
need to pause, or boost the background process priority
with dbengine.idle.

However, the best fix would be to replace the macro
with a single query that does all 100 000 appends in
one hit.

Show us your code and SQL.

(david)

 

[Guest]

Thanks David,
Unfortunately i have to run it like this as the table i extract the name's
history from has about 2 million entries and it just hangs way to long if i
try a query doing it all at once. This way i can simplify it and create sums
etc when i create the names history table a whole lot easier.
this dbengine idle thing sounds ok how do i go about that?
Here is my current code;
**************************************
Function Macro1()
On Error goto Macro1_err
Dim X as integer
x = 0
Do
If X < 100000 Then
Docmd.Setwarnings false
docmd.openquery "DeleteLastName"
DoCmd.OpenQuery "SelectName", acviewnormal, acEdit
DoCmd.OpenQuery "NameHistoryTable", acviewnormal, acEdit
DoCmd.OpenQuery "AppendNamewithhistorytoNewTable", acviewnormal, acEdit

'Next Name
X = X + 1
Else
End If
Loop

Macro1_Exit:
Exit Function
Macro1_Err:
MsgBox Error$
Resume Macro1_Exit
'Alert so i can hear its finished
Beep
Beep
Beep

Msgbox "Finished", vbOKOnly, ""

End Function
***********************************

 

[Guest]

Application.DBEngine.Idle

But what are you trying to do with docmd.openquery?

You need to try to run your queries like this:

set db = currentdb()

do
db.execute("DeleteLastName")
db.execute("AppendNamewithhistorytoNewTable")

db.execute is faster than docmd.

But opening and closing those queries is slower than
anything else you can do: there is no way that can be
faster than a single update query.

If you want to use a loop like you have shown (the
old fashioned way, but lots of work was done like
that) you need to open the query first, with
set rs = db.OpenRecordset("SelectName")
then use "movenext" to move to the correct record.

(david)

 

[Guest]

Thanks david, i'll give that a whirl. Makes sense.
Unfortunately Im a basic programmer! Getting better with this site though!
Cheers
Mark