?
Solved

Problem with sequential DoCmd's

Posted on 2016-09-27
9
Medium Priority
?
79 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 53

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 17

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 58

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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses

800 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