Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Problem with sequential DoCmd's

Posted on 2016-09-27
9
Medium Priority
?
88 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 - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 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 55

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
LVL 19

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 85
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 59

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

579 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