Forcing SQL to act sequentially

[telephoto]

I have a temp table which is used to generate reports, records are deleted until the specific criteria are met, then output to a report.

When this table is refreshed for a new search I have two SQL lines, the first deletes anything left in the table, the second refills it with data ready for the new search.

Access refuses to run the second line because it is still processing the first.

How can I force it to wait until the previous line's actions are finished?

Any ideas?

TIA

Telephoto

 

[Crowley16]

hmm not sure why you'd want to do a search that way but nonetheless...

do the 2 queries as docmd.runsql("insert your sql")
and put the 2nd query after the first...

 

[telephoto]

Thanks for your prompt response.

That is what I have done and it gives me the error statement. (Unable to execute the second command because someone else is using the table, which is the first command).

 

[Crowley16]

hmm...

that's strange...

how's about putting it in a loop and the exit condition would be if the table was freed up...

or you could just put a wait between the 2 statements, but this would mean that you'd know how long this procedure takes...

 

[RoyVidar]

I don't know if this works, but it would be interesting to know whether using the .execute method of the ADO connection object would work. It has the possibility of returning records affected by the executed query, so one guess is that it completes before the next line, but again, I don't know.

[tt]dim lRecAff as long
currentproject.connection.execute strSQL, lRecAff
debug.print lRecAff[/tt]

In addition to Crowley16 suggestions:
I don't know if trying a DoEvents between the two statements would work in this case, but you could try.

Or another version, delete the whole temp table, and recreate it in stead (change your append query to a make table query)

Roy-Vidar

 

[telephoto]

Gotit!!

error 3197 (table in use by another user) was generated, The MS site (via Google and this forum!) states that this is usually caused by corrupt code/data in a (typically) memo field. shows as "#error"

An eyeball search of about 2000 records with 25 fields discovered it.

Thanks to all who responded

Telephoto