ExcelBanter - View Single Post - Excel VBA Query Using Access Database Hangs on 2nd Execute

Mike Knight

(Jamie Collins) wrote in message . com...
(Mike Knight) wrote ...

I will experiment with leaving the connection open.

Something else to try might be to execute multiple times in a VBA
module the .mdb when open in the MS Access UI, using the
CurrentProject.Connection object (which AFAIK is an 'always connected'
connection). I'm wondering if Excel is disconnecting to the .mdb but
the .mdb connection to the AS400 remains open and locked in some way.

Jamie.

--

Jamie,

Are you saying that I should try opening the Access file from Access
before executing a query?

The AS400 connection does not seem to be the problem. We
simultaneously executed the same Excel query (opened read only) on two
different machines. One of the machines would not execute the query a
second time, but the other one would. The one that would, at other
times, would not. Since both Excels were using the same Access
database, I don't believe the AS400 was locked.

I wonder if this is a "memory leak" problem - somewhat similar to the
Microsoft article 319998 concerning Excel sheets? I modified my code
to create a new instance of Excel as per article 246335, but the
problem still occurs. If I manually close the instance of Excel that
ran the VBA, manually open another instance, and re-run the VBA, no
problem seems to occur.

The reason I use Access is as follows. In Excel 97, DAO3.5, I could
connect directly to the AS400 and use VBA functions to manipulate and
"clean-up" AS400 data (padded spaces, convert decimals, etc.) for use
in Excel. After upgrading to Excel 2003, DAO3.6, the ability to
connect directly and use the functions seemed to go away for the most
part. Also, I could never get satisfactory results connecting with
ADO. Using ADO and Access 2003 as a go-between restored complete use
of VBA functions. Basically, Excel VBA writes the SQL for an Access
query.

Mike