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.
There are many of these Excel query files and they all work at the
same time on different users' machines.

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.

Is there a problem in my code? Am I doing something wrong?

Thanks,
Mike

Private Sub Execute_Query()
Dim X As Integer
Dim DB1 As ADODB.Connection
Dim RS1 As ADODB.Recordset
Const ConnectionStr As String =
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= Myserver Location Goes here\AS400 Fields.mdb"
Set DB1 = New ADODB.Connection
DB1.Open ConnectionStr

Set RS1 = New ADODB.Recordset
'=============================
RS1.Open QryStr, DB1
'=============================
'Copy the data
ActiveCell.Offset(1, 0).Select
ActiveCell.CopyFromRecordset RS1
RS1.Close
DB1.Close
Set RS1 = Nothing
Set DB1 = Nothing
End Sub

(Mike Knight) wrote ...

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.
There are many of these Excel query files and they all work at the
same time on different users' machines.

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.

Private Sub Execute_Query()
Dim X As Integer
Dim DB1 As ADODB.Connection
Dim RS1 As ADODB.Recordset
Const ConnectionStr As String =
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= Myserver Location Goes here\AS400 Fields.mdb"
Set DB1 = New ADODB.Connection
DB1.Open ConnectionStr

Set RS1 = New ADODB.Recordset
'=============================
RS1.Open QryStr, DB1
'=============================
'Copy the data
ActiveCell.Offset(1, 0).Select
ActiveCell.CopyFromRecordset RS1
RS1.Close
DB1.Close
Set RS1 = Nothing
Set DB1 = Nothing
End Sub

I can't think of a specific problem. I think you must isolate the
problem a bit more. What is QryStr e.g. a SELECT query, a stored
procedure? Have you tried doing something else with the Connection
object e.g. use its Execute method to UPDATE or INSERT to a test
table, rather than return a row set? Have you tried running a
different query/stored proc to return a different result set? Have you
tried keeping the connection open for the second call? (this may not
be an option in production if you have many workbooks connecting to
the .mdb)

Jamie.

--

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

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.
There are many of these Excel query files and they all work at the
same time on different users' machines.

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.

Private Sub Execute_Query()
Dim X As Integer
Dim DB1 As ADODB.Connection
Dim RS1 As ADODB.Recordset
Const ConnectionStr As String =
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= Myserver Location Goes here\AS400 Fields.mdb"
Set DB1 = New ADODB.Connection
DB1.Open ConnectionStr

Set RS1 = New ADODB.Recordset
'=============================
RS1.Open QryStr, DB1
'=============================
'Copy the data
ActiveCell.Offset(1, 0).Select
ActiveCell.CopyFromRecordset RS1
RS1.Close
DB1.Close
Set RS1 = Nothing
Set DB1 = Nothing
End Sub

I can't think of a specific problem. I think you must isolate the
problem a bit more. What is QryStr e.g. a SELECT query, a stored
procedure? Have you tried doing something else with the Connection
object e.g. use its Execute method to UPDATE or INSERT to a test
table, rather than return a row set? Have you tried running a
different query/stored proc to return a different result set? Have you
tried keeping the connection open for the second call? (this may not
be an option in production if you have many workbooks connecting to
the .mdb)

Jamie.

--

Jamie

Qrystr is a string variable containing my select statement.

I just tried the Execute method:
(Set RS1 = DB1.Execute(QryStr, , adCmdText))

Different queries using different AS400 Files (linked tables in Access
DB) have the same problems.

I will experiment with leaving the connection open.

Thanks for your reply.

Mike

(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 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

(Mike Knight) wrote ...

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.


Just a suggestion to try and isolate the source of the problem i.e. is
it the connection from Excel to the.mdb or the connection from .mdb to
the AS400 (might be worth checking the lock file for the lock file).

Ideally your code should be able to recover from a connection failure.
Review your ADO timeout properties and consider sinking your
connection/recordset objects in a class module and taking advantage of
asynchronous connection/processing. This may not help if you are
correct about Excel hanging.

Jamie.

--