Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 97
  • Last Modified:

Problem with sequential DoCmd's

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
Cindy Aitken
Asked:
Cindy Aitken
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
Ryan ChongCommented:
>>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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
John TsioumprisSoftware & Systems EngineerCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
Cindy AitkenAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Odd that DBEngine.Idle dbRefreshCache would be needed. I've never had an issue with this sequence of ops.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now