Slow append query

[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: 3AE2C136121D877 18A192615C4FF35 0FB5F091E488D99 E265911AE77F6CB C1C1)

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 acCmdConvertLin kedTableToLocal 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!
[imgnothumb]https://bytes.com/attachment.php? attachmentid=95 46[/imgnothumb]

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