Slow append query

[Nauticalgent]

Greetings Access Commandos,

While I was looking for a solution to my problem, I came across this thread:
https://bytes.com/topic/access/insights/907658-progress-indicator-access

And although I think giving your clients something to look at while the Access Gnomes are making sausage is ALWAYS a good idea, I still want to fix why my query, which used to run instantaneous (relatively anyway), is now like watching grass grow.

The setup is fairly straight forward but in the interest of full disclosure, I will provide some details.
We recently “upgraded” from Win7/Office 2010 to Win10/Office 2013 - more on that later.

My BE is on SQL server 2008 (soon to be 2014), however the two tables in question are not. And finally, I have been told that we will be upgrading our exchange server soon too - that bit may be the key.

The two tables in question: one is a linked Outlook Public Folder which holds our message traffic. The other is a local table which is almost a mirror image of the Public Folder. At the start of the process, I have an Append query that inserts the records from the Public Folder into the local table, once that is done, the sub steps through each record and parses the text into other tables and so on and so forth.

On any given session there may be about 50 records or so and from start to finish, it would take about 1 minute tops to complete.

That was then. Shortly, but not immediately, after the “upgrade” the process can now take up to 5 minutes and the only feedback the users get is “Access is Not Responding”... The users, convinced that something is wrong, would start pushing keys, clicking mouse buttons and last, but not least, the almighty CTL-ALT-DEL combo and then I would get to clean up the splendid mess that was made. Not really a bad thing, it did prompt me to put in safeguards against this but I am still at wits end.

I broke the Module into sections and was able to narrow down the bottle-neck which was the append query. Even when I get under the hood and try to open the linked table, it can take up to a full minute for the screen to render and records to show, and we are talking less than a 100 records - crazy.

Running the query within Access is even slower so I KNOW where the problem is. What I do not know is why there is latency and subsequently, what to do about it.

The local table does have a PK which I added in an attempt to speed things up. Point of clarification, i should say temp table becuase I purge it before I run the append query with no results. I have compacted and repaired the FE and even did a decompile in vain attempts to fix this issue.

If this is simply an issue with Win10 and our antiquated but soon to be upgraded exchange server, than I can wait it out and see what happens...but if there is something I am overlooking and have not thought of yet, I am ALL ears(eyes)...

Still going to implement the progress screen, simply of cool point value alone!

[PhilOfWalton]

Don't know about the speed problem, but I think you have a progress meter.
See
https://bytes.com/topic/access/insig...r-progress-bar

I use it in countless situations, but a very similar one to yours is downloading Web Pages of shares, extracting the prices, and inserting into a table.

If you need any help with that aspect of the problem, let me know

Phil

[Nauticalgent]

Thanks Phil and you are right, I do have your meter and it’s a fine piece of work. However, unless I am mistaken (quite possible), it can only update once a process, such as a query, has completed.

I am looking for something that assures the user that something is going on while that God awful slow qurry is running. I don’t think it’s possible for Access/VBA to do two things at once (ASSynchonous) but I do not mind being wrong...

[PhilOfWalton]

Absolutely wrong. This is a dynamic process as records / emails / web pages / anything where the total number to be processed is known and a counter can be incremented as each item is processed.

Initially you can program it to show the total number of EMails to be processed.

As each email is read, the progress meter will move along, changing colour as required, the number of records read and if you want, the header or sender of each email.
I would add that in the published version, the detail (header or sender of each email) is not available. On the version I use, that information shows up.

This is what it looks like after reading 33 of the 41 Web Pages

If you are still interested, I will update the zip file.

Phil

[twinnyfo]

Hey Phil,

Will your meter progress while the DB is delaying during the long append query? I know Nauticalgent mentioned that was also part of the delay.

[PhilOfWalton]

It updates as as record are processed.

I should have pointed out that it does not work with an Append Query, but does update on an "Add New, Update" type loop in VBA

Phil

[Nauticalgent]

I have come to that conclusion. I have considered changing over to a loop paradigm, for reasons other than this, but I am still on the fence.

Reading records and manipulating the information to populate other tables gets tricky when you’re dealing with a dynamic object such as an Outlook Public Folder. I can’t test it now but I am wondering if using a select query as my RS and looping through it instead would be my solution...at worst I could then use a progress meter to keep everyone entertained while the grass grows...

[PhilOfWalton]

That is exactly the method I use. Much easier to monitor where the delay is taking place.

I will update the progress meter to the latest version, and post that on Bytes over the weekend.

Phil

[zmbd]

It sucks that VBA doesn't seem to have an asynchronous call.

I've ran into this issue with Outlook/Access pulling from our corporate servers. The main bottleneck was the bandwidth between our local shadowing of the corporate server and the main server. This lag increased exponentially once we started rolling into Window7(64bit)-Enterprise while there were still older OS clients on the network. Things improved greatly once we finally updated the exchange server and all of the clients had moved to the newer OS; however, it's still monstrously slow!

Keep in mind... the outlook public folder has several dozen, or more users depending our your company size, accessing that data all at the same time and there's a lot of handshaking going on behind the scenes to allow all of these people to access that data.

Hopefully, the upgrades to your network will improve things.

You might consider one of my workarounds which was to open a form in dialog+modal with a message indicating that windows may show Access to be non-responsive while updating the records and that it may take up to 10 minutes - this forced the user to either ctrl-tab to the button or left-click to close the form before the query started. When I did this it eliminated the ctrl+alt+del / task-manager calls to end Access - your mileage may vary :)

[Nauticalgent]

Thanks for the feedback zmbd and your experience sounds very much like my own. I cross posted this in another forum this morning and some of their more prolific members are chiming in with some good advice, which I just may try.

At any rate, once Phil supplieshis latest and greatest, I will implement it in just for GP. But, by chance I am able to crack the latency nut, I will definitely post that solution here.

Really appreciate the feedback from all who have posted.

John

[PhilOfWalton]

Hi John

Your wish is my command.

See FormSimple. Probably you will need to set the Smoothness back to 200.

Phil

[Nauticalgent]

Thanks Phill, I don’t care what the others say about you, you’re aces with me!

[PhilOfWalton]

Whatever they say, it's malicious lies.

Phil

[Nauticalgent]

...uttered from the lips of craven curs!

[Rabbit]

@zmbd, actually, there are asynchronous queries.

Option Explicit
Private WithEvents AsyncConnection As ADODB.Connection
 
Private Sub Command1_Click()
    Const ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Data\Access\database1.mdb;Persist Security Info=False"
 
    Set AsyncConnection = New ADODB.Connection
 
    AsyncConnection.ConnectionString = ConnectionString
    AsyncConnection.Open
 
    Const sql As String = "SELECT * FROM table1"
 
    Call AsyncConnection.Execute(sql, , CommandTypeEnum.adCmdText Or ExecuteOptionEnum.adAsyncExecute)
End Sub
 
Private Sub AsyncConnection_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
    If (adStatus = adStatusOK) Then
        Me.List2.RowSource = pRecordset.Source
    End If
 
    If (pConnection.State = ObjectStateEnum.adStateOpen) Then
        pConnection.Close
    End If
End Sub

[PhilOfWalton]

As a matter of interest, because I was feeling desperately bored, I wrote a routine to extract the SentTo info from my Outlook Sent folder.

Now bearing in mind that everything is on my local machine & I am using standard Access tables, it took 12 seconds to update 277 records.

The progress meter went so fast, it was barely worth showing.

Phil

[NeoPa]

I've come to this late but there are good reasons to avoid processing action queries in a VBA loop.

One is that they can, and often do, run very much more slowly. The main kicker for me though, is that they're very hard to recover from if something goes awry.

Execute a query and allow progress only if all updates succeed. The system handles all that for you. Always be very careful of trying to do things yourself that Access typically does for you. In my experience you generally find out later, once you've learned things you didn't know at the time, why Access does it the way it does, and why you should have done too.

Rabbit introduces the idea of asynchronous queries. This seems like a good idea but there are caveats :

1. I don't believe there's any way to determine progress within that. Done or not is about the size of it if I'm correct (and Rabbit will tell us if not).
2. I know MS have changed their minds a few times on this, but DAO is now what they say they're relying on for Access recordset work.
   This may not mean that ADODB is dead, but it does mean there's unlikely to be progress there.
   Unfortunately, there are just some things that ADODB does that DAO doesn't, so I use it where I have to. Nevertheless, I avoid it where I don't. I doubt you'll get burned using it but just be aware of the status. For now at least though, the library still exists and is available.

You also mentioned about adding a PK to your temp table. Technically this will actually slow it down. Not always noticeably. Unlikely to be noticeable for only hundreds of records in a local Access table. However, a PK (and every other index) present on a table you add records to means each addition requires extra work to update each of the indices. Some situations actually benefit from removing indices prior to such an append and then adding them again once the process has completed.

This issue is exacerbated particularly when a clustered index is used, but I can find nothing that indicates Access even uses those so that shouldn't be an issue for you.

[zmbd]

+ Rabbit, I had forgotten about the ADOB - I've been using the DAO methods for far too long!

I'm going to have to see if I can implement that call through to Outlook, I had been just using the Outlook object and pulling directly from the public folders

[zmbd]

Just a thought here for a workaround:

I've tossed together an idea that will create a simple HTML document with a spinner, instance an IE object, load the HTML into the IE, and then display the document.

You could just simply open the GIF directly in IE; however, I thought a bit of user text would be nice to have...

By essentially shelling the busy-icon out we can allow Access to run in its thread while giving a bit of user feedback!

I've tried this on my slow-running Outlook query and it works like a dream (the ADODB is having issues connecting to the Enterprise server (most likely I don't have the proper credentials :-(
) and I may just roll it out to my production database(s)!

Someone can bodge together a much more elegant HTML document that could be directly loaded instead of hardcoding the HTML and creating the file VIA vba.

There are ways (IIRC) to alter the text on the HTML directly via the DOM objects if you wanted to provide more feedback (provided your query/code releases long enough!)... personally I'd just create or use a separate HTML text file as the last time I played with the IE DOM via VBA it was a bit of a headache.

So the attached... Down load :), Please use your antivirus software to double check things, extract, open the database... click on the form... the code will run etc.... When you close the form (just use the upper x), if the IE is still open then the code will close it and clean up.
(the SHA256 for the zipped file is: 3AE2C136121D87718A192615C4FF350FB5F091E488D99E2659 11AE77F6CBC1C1)

Should be a very simple thing to import to an existing database, just remember to move the Loading Gif... or perhaps link to one of the loading gifs in the creative commons or wiki commons if your clients have internet access.

-Z

[PhilOfWalton]

@ Nauticalgent

Here is some code that will read your outlook folder and load something into a table.

It shows the Progress Meter.

There is no error checking and I have no idea what your table looks like. so there may be further refinements needed to perhaps check the dates of the Emails and decide what to do with errors (Duplicates are a possibility

Option Compare Database
Option Explicit
 
    Dim PMeter As New ClsPMeterRainbow
 
Function ListMailsInFolder(SubFolderName As String)
' ? ListMailsInFolder("Sent")
 
    Dim objNS As Outlook.NameSpace
    Dim objFolder As Outlook.MAPIFolder
    Dim Item As Outlook.MailItem
    Dim LngCounter As Long
    Dim ShowInfo As Boolean
 
    Dim MyDb As Database
    Dim SentToSet As Recordset
    Dim StrSQL As String
 
    ' Fields for Progress Meter
    Dim PMFrmCaption As String          ' Caption
    Dim PMFrmTitle As String            ' Title
    Dim PMFrmToProcess As String
    Dim PMFrmProcessed As String
    Dim PMFrmProcessing As String
    Dim PMFrmMax As Long
    Dim FromColour As Long
    Dim ToColour As Long
    Dim RecordNo As Long
    Dim Smoothness As Integer
 
    Set objNS = GetNamespace("MAPI")
    Set objFolder = objNS.Folders.GetFirst ' folders of your current account
    Set objFolder = objFolder.Folders("Inbox").Folders(SubFolderName)
 
    StrSQL = "SELECT TblOutlookSentTo.* FROM TblOutlookSentTo"
    Set MyDb = CurrentDb
    Set SentToSet = MyDb.OpenRecordset(StrSQL)
Start:
    If ShowInfo = True Then
        ' Set default caption, label titles maxima colours
        FromColour = vbRed              ' Default from colour
        ToColour = vbGreen              ' Default to colour
        PMFrmCaption = "Phil's progress meter"
        PMFrmTitle = "Please wait . . . "
        PMFrmToProcess = "Total Emails:"
        PMFrmProcessed = "Emails Read:"
        PMFrmProcessing = "Sent To:"
        Smoothness = 1          ' Play with this to get a sensible display (Depends on no of records)
                                ' Default is 200
        ' Pass the opening instructios to the Progress Meter
        PMeter.RainShowProgress
        PMeter.RainSetCaption = PMFrmCaption            ' Progress Meter Caption
        PMeter.RainSetTitle = PMFrmTitle                ' Progress Meter Title
        PMeter.RainSetLabToProcess = PMFrmToProcess     ' Progress Meter To Process
        PMeter.RainSetLabProcessed = PMFrmProcessed     ' Progress Meter Number Processed
        PMeter.RainSetLabProcessing = PMFrmProcessing   ' Progress Meter Item being processed
        PMeter.RainSetFromColour = FromColour           ' Default from colour
        PMeter.RainSetToColour = ToColour               ' Default to colour
        PMeter.RainSetToProcess = PMFrmMax              ' RecordCount
        PMeter.RainSetSmoothness = Smoothness           ' Smoothness
    End If
 
    For Each Item In objFolder.Items
        If TypeName(Item) = "MailItem" Then
            LngCounter = LngCounter + 1
            ' ... do stuff here ...
            With SentToSet
                .AddNew
                !SentTo = Left(Item.To, 255)            ' Length limitation
                .Update
            End With
            If ShowInfo = True Then
                RecordNo = RecordNo + 1
                ' formulae to change the colour of the Progress Meter
                If RecordNo Mod Smoothness = 0 Then
                ' Change the colour of the Progress Meter
                    PMeter.RainSetProcessing = Item.To      ' Record being processed
                    PMeter.RainIncOne (RecordNo)
                End If
            End If
        End If
    Next
 
    If ShowInfo = False Then
        PMFrmMax = LngCounter
        Debug.Print "No of mail items: " & LngCounter
        LngCounter = 0
        ShowInfo = True
        GoTo Start
    Else
        PMeter.RainHideProgress                         ' Close the Progress Meter
        SentToSet.Close
        Set SentToSet = Nothing
    End If
 
End Function
 

Phil

[Nauticalgent]

Hello SportsFans, sorry for the delayed followup but I have been throwing spit-balls at the wall do see what stuck and here is what I have come up with:

ZMBD hit the proverbial nail on the head in post # 9. When dealing with Default Public Folders in Outlook, there is simply too much going on that is out of your control. My particular folder has to be synced with 80+ members and the communication with the exchange server is just too much. Maybe after the upgrade things will improve but for now, I am in the Land of Suck.

I attempted a few suggestions from this site and another and I will summarize them.

Importing / Relinking / Using a make table query. All worked of course but they did not improve the speed. Put simply, it is the Outlook Server and not Access that is causing the delay. I even researched and learned about the acCmdConvertLinkedTableToLocal command and although pretty cool, it had problems of its own - and the BLOAT from the import and make table queries! Forget about it. Bottom line: Huge investment, little to no return.

However, all was not lost. Discussions with Phil about using select queries as recordsets and then looping through them in instead of using batch queries led me down a path that helped a great deal. At this point I didn’t care about speed, I just wanted a visual reassurance to the users that something was going on and a loop paradigm would allow me to do that.

I was shocked and pleasantly surprised at the outcome. By using my own UDF’s in my queries, I was able to “trim the fat” and leave only the white meat prior to my loop which really cut down the setup time – almost by half!

But it would still take a minute or two for the setup (the select queries to return a recordset for processing) and I really needed a toy for the users to play with before I could deploy Phil’s Progress Meter (excellent job BTW!)

Again, ZMBD came to the rescue in post #19 with that HTML-roundy-roundy thingy. This is EXACTLY what I was looking to use for those times when the Access Gnomes are being mischievous. I use it until the set-up is done and then switch to Phil’s meter. The process happens so quickly that if you blink you may miss the meter: It doesn’t take Access long to step through 20 some-odd records but it sure is cool!

For that reason, I gave ZMBD’s reply the “Best Answer” award, but I wish I could co-award it to Phil. It was his input that helped me make the overall process much more efficient and the skills I learned over the past week are going to come in handy in the future.

The combination of these suggestions has really given my app a more professional look. Thanks guys, this is how these forums are supposed to work.

p.s. ZMBD, I would like to share your gadget with some members from another forum who also had a hand in helping me suss this out. I never use someone else’s idea without their permission and although I am no champion of integrity, but I do have my boundaries.

No-harm, no-foul if you say no.

[zmbd]

Rather than just giving others the zip file or the code, would instead point them to this thread?

This way they will get the benefit of PhilOfWalton's work, access to our other experts for issues they may run into, our members may get access to their knowledge (everyone has a different view!), and my own work - every one wins!

[Nauticalgent]

Even better! Will do and thanks again.

[Nauticalgent]

Had to revisit this post to share some things I learned today. First of all, ZMBD: It was your sample DB that put me over the top and I thank you again!

That being said, your code did not work for me as I has hoped. The web page would not close and when I commented the

On Error Resume Next

bit, I got this error: "462 - The remote server machine does not exist or is unavailable"

Additionally, for some reason, at work I would receive a different error: “The object invoked has disconnected from its clients”

A series of Google searches showed me that I needed a "MicroSoft Internet Controls Reference" and I needed to replace

Private zIE As Object
Set zIE = CreateObject("internetexplorer.application")

...with

Private zIE As InternetExplorer
Set zIE = New InternetExplorerMedium

No idea why one works and the other doesn't, but I know it does.

I attached a copy of a sample DB for others to see. If anyone sees something I could have done more efficiently, I am ALL ears (eyes)

[twinnyfo]

NauticalGent,

What you are experiencing is a failsafe built into VBA so that you have to refer to objects explicitly each time, otherwise the engine keeps track of that object and can't let it go.

I'm sure I am neither explaining it well, nor completely accurately. However, I've have recently experienced Error 462 repeatedly (particularly when you re-run a bit of code that you KNOW works perfectly), and as long as I refer explicitly to these automation objects, I am fine.

Hope this hepps!

[Nauticalgent]

What you are experiencing is a failsafe built into VBA so that you have to refer to objects explicitly each time

So wouldn't zIE.Quit not make a proper reference then?

So what did you think of the code in general?

[twinnyfo]

My comment was specifically geared toward Error 462. I am unable to download the DB from work, so I can't verify anything. However, I will give you an example.

Automating MS Word from within MS Access (more than you were asking for here):

Option Explicit
Option Compare Database
 
Public Sub OpenWordDoc()
    Dim wdApp   As Word.Application
    Dim wdDoc   As Word.Document
    Dim strDoc  As String
 
    strDoc = "C:\MyFile.docx"
 
    'Open the Word App, and the working file
    On Error Resume Next
    Set wdApp = GetObject(Class:="Word.Application")
    If Err.Number <> 0 Then    'Word isn't running so start it
        Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo EH
 
    Set wdDoc = wdApp.Documents.Open(strWordFile)
    wdApp.Visible = True
 
    With Selection
        .TypeText Text:="Hello World"
        .TypeParagraph
    End With
 
    wdDoc.Save
    Set wdDoc = Nothing
    wdApp.Quit
    Set wdApp = Nothing
 
    Exit Sub
EH:
EH:
    MsgBox "There was an error opening Word!" & vbCrLf & vbCrLf & _
        "Error: " & Err.Number & vbCrLf & _
        "Description: " & Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End Sub

This code will work WITHOUT FAIL! But only the first time.

The second time I run this code, I get Error 462.

However, if I change line 22 to:

    With wdApp.Selection

I can run it successive times. If not, Access "holds on" to wdApp and doesn't release it, even though you have set it to nothing.

I just wanted to help explain why you might be experiencing Error 462 and hope this might help others who run across this.