(I've also posted this problem on microsoft.public.excel.programming)
I have a MS Access 2003 Database named "AS400 Fields.mdb". This
database contains links to tables on an AS400.
In MS Excel 2003, I have VBA code that creates and executes queries
using the Access database, and returns the results to an Excel sheet.
The first time the query is executed, results are returned to Excel in
usually less than 10 seconds. However, if the query is executed a
second time (without closing the Excel workbook), the code hangs at
the line surrounded by "=",s in the code below (this was tested with
message boxes). If the Excel file containing the VBA code is closed
and re-opened the code executes normally.
(Some statements may be unnecessary - they are attempts at resolving
the issue)
Private Sub Execute_Query()
Dim Db As Database
Dim Qd As QueryDef
Dim Rs As Recordset
Dim x As Integer
Const strDatabaseName As String = "My Server Location Goes
Here\AS400 Fields.mdb"
Set Db = Workspaces(0).OpenDatabase(strDatabaseName)
On Error Resume Next
Db.QueryDefs.Delete ("TempQry")
On Error GoTo 0
Set Qd = Db.CreateQueryDef("TempQry", QryStr)
Qd.ODBCTimeout = 0
'================================================= =======
Set Rs = Qd.OpenRecordset()
'================================================= =======
Rs.MoveLast
Rs.MoveFirst
'Read in the field names
With ActiveSheet
For x = 0 To Rs.Fields.Count - 1
.Cells(ActiveCell.Row, x + 1).Value = Rs.Fields(x).Name
Next
End With
Rs.MoveFirst
'Copy the data
ActiveCell.Offset(1, 0).Select
ActiveCell.CopyFromRecordset Rs
Rs.Close
Qd.Close
Db.QueryDefs.Delete ("TempQry")
Db.Close
Set Qd = Nothing
Set Rs = Nothing
Set Db = Nothing
DBEngine.Idle dbFreeLocks
DBEngine.Workspaces(0).Close
End Sub
Several considerations are noted.
The database is compacted and has remained at 16,488KB - no data is
actually stored from Excel queries in the Access database - only
linked tables to the AS400 and the temporary query definition.
I have two computers side by side, both connected to the server.
On computer 1, I opened Excel and the file, ran the code getting
desired results. Without closing Excel or the file, I re-ran the
program and no data or error was returned after an appropriate time -
the hour-glass was displayed. I did not stop the code (this can't be
done with control break), but left Excel and the file "running" and
moved to computer 2.
On computer 2, I opened read-only the same file as on computer 1 from
the server location (only after computer 1 hung-up). I ran the code
and received the same results as were received the first time the code
was run on computer 1, within seconds. The code on computer 2 was
executed again and the computer "hung-up".
Note that both computers were executing the same code from the same
server file, and that both computers were using the same Access
Database on the server. It doesn't appear that an AS400 connection
problem within Access exists - computer 2 was able to use the DB even
while Computer 1 was using it. Both computers are Intel Pentium 4
2.66GH 248MB Windows XP SP1 Office 2003.
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.
Other MS KB articles discuss problems with linked tables (ID 208379),
but so far, nothing seems to help.
Thanks for your input - any suggestions are appreciated.
Mike
