Solved

Problem with sequential DoCmd's

Posted on 2016-09-27
9
49 Views
Last Modified: 2016-09-28
I'm running three DoCmd's, one after the other -- a delete query to clear the table, an append query to add new data, and finally an OpenTable command to view the new data.  The delete query and OpenTable command both work, but the append query doesn't.  When the table opens, all the records have been deleted, but if I close and reopen the table, the new data from the append query shows up.

Do I need to open and close the table between the delete and append queries?
0
Comment
Question by:Cindy Aitken
9 Comments
 
LVL 75
ID: 41819080
When you first open the table and see Deleted ... if you then hit Refresh All on the Ribbon, what happens.
And no, you should not have to open the table between operations.  I do the Delete/Append scenario in many different import routines I have.

mx
1
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
ID: 41819084
And if that doesn't work, try this

CurrentDB.Execute "YourDeleteQueryName", dbFailOnError
CurrentDB.Execute "YourAppendQueryName", dbFailOnError
DoCmd.OpenTable "YourTableName
If either of the first to operations fail, you will see what error is occurring.
1
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41819086
>>Do I need to open and close the table between the delete and append queries?

how your codes look like?

yea, you may close the Table before you show it with newly appended data. To close a table, you can use:
DoCmd.Close acTable, "yourTableName"

Open in new window

1
 
LVL 75
ID: 41819167
There is no need to open and close the table between the Delete and Append operations.
We do this daily at work ...
Delete existing data
Append new data
Open table so user can examine the data.

Something odd is going on in your case ...
1
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41819180
Maybe there is some latency that causes this
You could try to insert a Sleep between each Docmd and check if this helps
So in a module insert this
Declare Sub Sleep Lib "kernel32" Alias "Sleep" _
(ByVal dwMilliseconds As Long)
Pubic Sub Sleep( SleepTimeInMilliseconds as Long)
Sleep SleepTimeInMilliseconds 
End Sub 

Open in new window

Then after each Docmd
Docmd ...Whatever
Sleep 500

Open in new window

1
 
LVL 84
ID: 41819593
I think Joe's suggestion will work, but if you want to make sure your Delete Query is completed before moving on, you can do this:

<your delete query here>

Dim reccnt as Integer
Do Until reccnt = 0
  reccnt = DCOUNT("SomeField", "TheTableYouAreEmptying")  
Loop

<your next query here>
1
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 41819612
Two comments:

1. You always want to use the Execute method with dbFailOnError as Joe showed.   With DoCmd's, it's too easy for an error to be bypassed (usually you setwarnings off, but then you don't see any errors).

2. Regarding letting Access catch-up on processing, putting it to sleep is not the answer.  You'd only do that if you were waiting for another application to finish (say generate a bar code label or process an image). Instead, you want to do:

 DBEngine.Idle dbRefreshCache

 This let's JET/ACE catch up on all processing before the next statement is executed. From:

https://msdn.microsoft.com/en-us/library/office/ff823202.aspx

"The Idle method allows the Microsoft Access database engine to perform background tasks that may not be up-to-date because of intense data processing. This is often true in multiuser, multitasking environments that don't have enough background processing time to keep all records in a Recordset current."

Jim.
1
 

Author Comment

by:Cindy Aitken
ID: 41819986
I added "dbFailOnError" (I'm neglectful of error checking in db's for personal use, like this one).  No errors were found.  I also added "DBEngine.Idle dbRefreshCache".  That must've been the ticket.  The table is opening with the new data appended now.

Many thanks for all the responses!  Even the suggestions I didn't try taught me something.
0
 
LVL 75
ID: 41820251
Odd that DBEngine.Idle dbRefreshCache would be needed. I've never had an issue with this sequence of ops.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now