Jim Lawrence
accessd at shaw.ca
Thu Oct 27 00:42:53 CDT 2005
John it is just as annoying when trying to export data from a legacy DB application and the only capable export methods are text based. And you just know that some record is some field in some table will have a 'quote' mark in it. So queries for all text fields have to be created with the one purpose finding these elusive bugs... It all seems like a long time ago...like last week. :-) Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby Sent: Wednesday, October 26, 2005 5:18 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Preventing Extra Excel instances SOLVED I had one today... I build a SQL statement as the recordsource for a form. I am dynamically building up a where clause where I substitute in the field name and value in the where clause. A name ( O'Doule or some such) has an apostrophe in it. As you know, the ' is the syntax for enclosing text strings in a SQL statement when doing this.... "Some SELECT HERE" & _ "WHERE SomeField = '" & strSomeValue & "';" In many of the computers at the client, when I append the SQL string in the form's recordsource with an ' in the middle, instead of a run time error, Access gives a warning and shuts down. On my machine it correctly gives a runtime error. I am SOOOOooooo tired of this buggy program. John W. Colby www.ColbyConsulting.com Contribute your unused CPU cycles to a good cause: http://folding.stanford.edu/ -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby Sent: Wednesday, October 26, 2005 7:43 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Preventing Extra Excel instances SOLVED You need to report this one to Microsoft. John W. Colby www.ColbyConsulting.com Contribute your unused CPU cycles to a good cause: http://folding.stanford.edu/ -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence Sent: Wednesday, October 26, 2005 7:24 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Preventing Extra Excel instances SOLVED Hi Jim: Excellent deduction. I have been annoyed by that issue for years. Truly a piece of brilliance. Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim Sent: Wednesday, October 26, 2005 8:09 AM To: 'Accessd (E-mail) Subject: [AccessD] Preventing Extra Excel instances SOLVED As I have commented on several occasions, I've had a pesky problem with orphan Excel instances that are not destroyed when the program finishes. As several of you pointed out all object variables must explicitly be set to nothing. While this cured some cases I have continued to have Excel instances that refuse to die. I have finally traced the cause to the following code fragments: Public Function UpdateLoan(appXcel As Excel.Application) Dim rng(1 To 2) As Range With appXcel Do While Not IsEmpty(ActiveCell) ............. Set rng(1) = .Range(Cells(2, intCopycol), Cells(47, intCopycol)) The following code fragments are the culprit: isEmpty(ActiveCell) and .Range(Cells(2, intCopycol), Cells(47, intCopycol)) The code works fine but leaves the Excel instance in memory. a period (.) before ActiveCell and Cells solves the problem, ie.: isEmpty(.ActiveCell) .Range(.Cells(2, intCopycol), .Cells(47, intCopycol)) Apparently global members of the Excel application object such as ActiveCell and Cells must be explicitly qualified with the Application object for them to be properly destroyed when they go out of scope. Even though the program will run correctly without them being qualified, they are not cleaned up properly when used this way. Exactly what is happening internally wiser heads than I may want to comment. Jim Hale *********************************************************************** The information transmitted is intended solely for the individual or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you have received this email in error please contact the sender and delete the material from any computer. As a recipient of this email, you are responsible for screening its contents and the contents of any attachments for the presence of viruses. No liability is accepted for any damages caused by any virus transmitted by this email. -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com