Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Problem with sequential DoCmd's

Posted on 2016-09-27
9
Medium Priority
?
82 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

610 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