[AccessD] Preventing Extra Excel instances SOLVED

John Colby jwcolby at ColbyConsulting.com
Wed Oct 26 19:18:26 CDT 2005


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





More information about the AccessD mailing list