423,682 Members | 1,348 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,682 IT Pros & Developers. It's quick & easy.

Slow append query

Nauticalgent
P: 73
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!
Jul 6 '18 #1

✓ answered by 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

Share this Question
Share on Google+
26 Replies


PhilOfWalton
Expert 100+
P: 1,373
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
Jul 6 '18 #2

Nauticalgent
P: 73
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...
Jul 6 '18 #3

PhilOfWalton
Expert 100+
P: 1,373
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
Jul 6 '18 #4

twinnyfo
Expert Mod 2.5K+
P: 2,667
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.
Jul 6 '18 #5

PhilOfWalton
Expert 100+
P: 1,373
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
Jul 6 '18 #6

Nauticalgent
P: 73
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...
Jul 7 '18 #7

PhilOfWalton
Expert 100+
P: 1,373
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
Jul 7 '18 #8

zmbd
Expert Mod 5K+
P: 5,285
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 :)
Jul 7 '18 #9

Nauticalgent
P: 73
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
Jul 7 '18 #10

PhilOfWalton
Expert 100+
P: 1,373
Hi John

Your wish is my command.

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

Phil
Jul 7 '18 #11

Nauticalgent
P: 73
Thanks Phill, I don’t care what the others say about you, you’re aces with me!
Jul 7 '18 #12

PhilOfWalton
Expert 100+
P: 1,373
Whatever they say, it's malicious lies.

Phil
Jul 7 '18 #13

Nauticalgent
P: 73
...uttered from the lips of craven curs!
Jul 7 '18 #14

Rabbit
Expert Mod 10K+
P: 12,272
@zmbd, actually, there are asynchronous queries.

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Private WithEvents AsyncConnection As ADODB.Connection
  3.  
  4. Private Sub Command1_Click()
  5.     Const ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Data\Access\database1.mdb;Persist Security Info=False"
  6.  
  7.     Set AsyncConnection = New ADODB.Connection
  8.  
  9.     AsyncConnection.ConnectionString = ConnectionString
  10.     AsyncConnection.Open
  11.  
  12.     Const sql As String = "SELECT * FROM table1"
  13.  
  14.     Call AsyncConnection.Execute(sql, , CommandTypeEnum.adCmdText Or ExecuteOptionEnum.adAsyncExecute)
  15. End Sub
  16.  
  17. 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)
  18.     If (adStatus = adStatusOK) Then
  19.         Me.List2.RowSource = pRecordset.Source
  20.     End If
  21.  
  22.     If (pConnection.State = ObjectStateEnum.adStateOpen) Then
  23.         pConnection.Close
  24.     End If
  25. End Sub
Jul 7 '18 #15

PhilOfWalton
Expert 100+
P: 1,373
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
Jul 8 '18 #16

NeoPa
Expert Mod 15k+
P: 31,084
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.
Jul 8 '18 #17

zmbd
Expert Mod 5K+
P: 5,285
+ 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
Jul 9 '18 #18

zmbd
Expert Mod 5K+
P: 5,285
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
Attached Files
File Type: zip bytesthread_971000_SlowAppend_UseIeForBusyIcon.zip (73.8 KB, 13 views)
Jul 9 '18 #19

PhilOfWalton
Expert 100+
P: 1,373
@ 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

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4.     Dim PMeter As New ClsPMeterRainbow
  5.  
  6. Function ListMailsInFolder(SubFolderName As String)
  7. ' ? ListMailsInFolder("Sent")
  8.  
  9.     Dim objNS As Outlook.NameSpace
  10.     Dim objFolder As Outlook.MAPIFolder
  11.     Dim Item As Outlook.MailItem
  12.     Dim LngCounter As Long
  13.     Dim ShowInfo As Boolean
  14.  
  15.     Dim MyDb As Database
  16.     Dim SentToSet As Recordset
  17.     Dim StrSQL As String
  18.  
  19.     ' Fields for Progress Meter
  20.     Dim PMFrmCaption As String          ' Caption
  21.     Dim PMFrmTitle As String            ' Title
  22.     Dim PMFrmToProcess As String
  23.     Dim PMFrmProcessed As String
  24.     Dim PMFrmProcessing As String
  25.     Dim PMFrmMax As Long
  26.     Dim FromColour As Long
  27.     Dim ToColour As Long
  28.     Dim RecordNo As Long
  29.     Dim Smoothness As Integer
  30.  
  31.     Set objNS = GetNamespace("MAPI")
  32.     Set objFolder = objNS.Folders.GetFirst ' folders of your current account
  33.     Set objFolder = objFolder.Folders("Inbox").Folders(SubFolderName)
  34.  
  35.     StrSQL = "SELECT TblOutlookSentTo.* FROM TblOutlookSentTo"
  36.     Set MyDb = CurrentDb
  37.     Set SentToSet = MyDb.OpenRecordset(StrSQL)
  38. Start:
  39.     If ShowInfo = True Then
  40.         ' Set default caption, label titles maxima colours
  41.         FromColour = vbRed              ' Default from colour
  42.         ToColour = vbGreen              ' Default to colour
  43.         PMFrmCaption = "Phil's progress meter"
  44.         PMFrmTitle = "Please wait . . . "
  45.         PMFrmToProcess = "Total Emails:"
  46.         PMFrmProcessed = "Emails Read:"
  47.         PMFrmProcessing = "Sent To:"
  48.         Smoothness = 1          ' Play with this to get a sensible display (Depends on no of records)
  49.                                 ' Default is 200
  50.         ' Pass the opening instructios to the Progress Meter
  51.         PMeter.RainShowProgress
  52.         PMeter.RainSetCaption = PMFrmCaption            ' Progress Meter Caption
  53.         PMeter.RainSetTitle = PMFrmTitle                ' Progress Meter Title
  54.         PMeter.RainSetLabToProcess = PMFrmToProcess     ' Progress Meter To Process
  55.         PMeter.RainSetLabProcessed = PMFrmProcessed     ' Progress Meter Number Processed
  56.         PMeter.RainSetLabProcessing = PMFrmProcessing   ' Progress Meter Item being processed
  57.         PMeter.RainSetFromColour = FromColour           ' Default from colour
  58.         PMeter.RainSetToColour = ToColour               ' Default to colour
  59.         PMeter.RainSetToProcess = PMFrmMax              ' RecordCount
  60.         PMeter.RainSetSmoothness = Smoothness           ' Smoothness
  61.     End If
  62.  
  63.     For Each Item In objFolder.Items
  64.         If TypeName(Item) = "MailItem" Then
  65.             LngCounter = LngCounter + 1
  66.             ' ... do stuff here ...
  67.             With SentToSet
  68.                 .AddNew
  69.                 !SentTo = Left(Item.To, 255)            ' Length limitation
  70.                 .Update
  71.             End With
  72.             If ShowInfo = True Then
  73.                 RecordNo = RecordNo + 1
  74.                 ' formulae to change the colour of the Progress Meter
  75.                 If RecordNo Mod Smoothness = 0 Then
  76.                 ' Change the colour of the Progress Meter
  77.                     PMeter.RainSetProcessing = Item.To      ' Record being processed
  78.                     PMeter.RainIncOne (RecordNo)
  79.                 End If
  80.             End If
  81.         End If
  82.     Next
  83.  
  84.     If ShowInfo = False Then
  85.         PMFrmMax = LngCounter
  86.         Debug.Print "No of mail items: " & LngCounter
  87.         LngCounter = 0
  88.         ShowInfo = True
  89.         GoTo Start
  90.     Else
  91.         PMeter.RainHideProgress                         ' Close the Progress Meter
  92.         SentToSet.Close
  93.         Set SentToSet = Nothing
  94.     End If
  95.  
  96. End Function
  97.  
Phil
Jul 9 '18 #20

Nauticalgent
P: 73
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.
Jul 14 '18 #21

zmbd
Expert Mod 5K+
P: 5,285
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!

Attached Images
File Type: png GreenSmile.png (5.9 KB, 253 views)
Jul 14 '18 #22

Nauticalgent
P: 73
Even better! Will do and thanks again.
Jul 14 '18 #23

Nauticalgent
P: 73
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
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. Private zIE As Object
  2. Set zIE = CreateObject("internetexplorer.application")
...with
Expand|Select|Wrap|Line Numbers
  1. Private zIE As InternetExplorer
  2. 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)
Attached Files
File Type: zip Bytes971000_NG.zip (108.4 KB, 7 views)
4 Weeks Ago #24

twinnyfo
Expert Mod 2.5K+
P: 2,667
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!
3 Weeks Ago #25

Nauticalgent
P: 73
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?
3 Weeks Ago #26

twinnyfo
Expert Mod 2.5K+
P: 2,667
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):

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Option Compare Database
  3.  
  4. Public Sub OpenWordDoc()
  5.     Dim wdApp   As Word.Application
  6.     Dim wdDoc   As Word.Document
  7.     Dim strDoc  As String
  8.  
  9.     strDoc = "C:\MyFile.docx"
  10.  
  11.     'Open the Word App, and the working file
  12.     On Error Resume Next
  13.     Set wdApp = GetObject(Class:="Word.Application")
  14.     If Err.Number <> 0 Then    'Word isn't running so start it
  15.         Set wdApp = CreateObject("Word.Application")
  16.     End If
  17.     On Error GoTo EH
  18.  
  19.     Set wdDoc = wdApp.Documents.Open(strWordFile)
  20.     wdApp.Visible = True
  21.  
  22.     With Selection
  23.         .TypeText Text:="Hello World"
  24.         .TypeParagraph
  25.     End With
  26.  
  27.     wdDoc.Save
  28.     Set wdDoc = Nothing
  29.     wdApp.Quit
  30.     Set wdApp = Nothing
  31.  
  32.     Exit Sub
  33. EH:
  34. EH:
  35.     MsgBox "There was an error opening Word!" & vbCrLf & vbCrLf & _
  36.         "Error: " & Err.Number & vbCrLf & _
  37.         "Description: " & Err.Description & vbCrLf & vbCrLf & _
  38.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  39.     Exit Sub
  40. 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:

Expand|Select|Wrap|Line Numbers
  1.     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.
3 Weeks Ago #27

Post your reply

Sign in to post your reply or Sign up for a free account.