"TK" wrote in message ...
Hi Mike:

Let me add a couple more suggestions

I put some error handling in your procedure and a connection
test. The procedure did not fail as tested on this computer but perhaps
a procedure on a different computer may have a glitch and the error
handler should fail it..

Is there a need to declare the connection string as Const. It could cause
problems if declared out of the procedure.

One other consideration I have found is if none of your procedures calls
the Access repair and compact utility the DB can get huge and slow..
If you are not familiar with the utility its Access Tools/DataBase Utilites/
Compact and Repair.

If you check the file size in explorer before and after you see if it was
necessary.

Private Sub Execute_Query()

On Error GoTo ErrHandler

Dim X As Integer

Dim DB1 As ADODB.Connection
Dim RS1 As ADODB.Recordset

'Is there a need to declare this as a Const if it
'gets declared outside of the procedure you have form scope

Const ConnectionStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= Myserver Location Goes here\AS400 Fields.mdb"
'I prefer
Dim Connections As String
ConnectionStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= Myserver Location Goes here\AS400 Fields.mdb"

Set DB1 = New ADODB.Connection
DB1.Open ConnectionStr

'Test to see if we are connected
If DB1.State = adStateOpen Then
MsgBox "Welcome to! "
Else
MsgBox "No Data today."
End If

Set RS1 = New ADODB.Recordset
'=============================
RS1.Open strSQL, DB1
'=============================
'Copy the data
ActiveCell.Offset(1, 0).Select
ActiveCell.CopyFromRecordset RS1

RS1.Close
DB1.Close

'Test to see if we are still connected
If DB1.State = adStateOpen Then
MsgBox "Welcome to! "
Else
MsgBox "Where Closed!"
End If

Set RS1 = Nothing
Set DB1 = Nothing

Exit Sub

ErrHandler:
MsgBox "Sorry, an error occured. " & Err.Description, vbOKOnly

End Sub

Good Luck
TK

TK,

Sorry I'm so late in responding - accounting period end activities got
in the way!

First, 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.

Second, I removed the code declaring the connection string as a
constant, DIMed the variable, and set it equal to the provider + data
source.

Third, I added the Msgbox statements and the error checking.

Fourth, the file was saved on the server and Excel closed.

I have two computers side by side, both connected to the server.

On computer 1, I opened Excel and the file, ran the changed code and
the messages were displayed: "Welcome to" and "Where Closed" (after
data was returned). Without closing Excel or the file, I re-ran the
program and got the message "Welcome to". 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".

On computer 2, I opened read-only the same file as on computer 1 from
the server location (only after computer1 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.

Thanks for your input - any suggestions are appreciated.

Mike