point taken. actually, i am using the exact same conn method as you did except my connection string is a little diff from yours. i did check those syntax for...

thank you for your diligent help, lisa. i really appreciated.

1) i can afford to leave out the temp table but couldn't understand why it won't work that way. it could...

after few more trial and error, the results were not encouranging. here were my test runs i have tried.

1) i left out my condition...

Remember earlier I modifed my sproc so that it does not require any parameter input. Instead I hardcoded the parameters inside my sproc just to test.

Here is the modified portion...

Ready? Here you go. This is the orig stored proc with the two parameters:

USE Septage

GO

CREATE PROCEDURE dbo.proc_Charge

(

 @Start datetime,

 @End datetime

)

/*

Object: proc_Charge

Description: Get charges per customer including penalty if any

Usage: proc_Charge @Start,...

ok, Lisa. I did exactly what you showed there and I still received the same error message, "Operation is not allowed when the object is closed".

At this point, I believe...

thank you, cje and lisa. I figured what the problem is. It is the stored procedure.

The way I figured it out is to...

ok, Lisa. The execute only allows these parameter:

Execute([RecordsAffected], [Paramaters], [Options as Long = -1)

Here is the revision for the 1st suggestion:

if iStatus = 1 then

  With cmd

    .Activeconnection = conn

   ...

ok, cje. I tried your suggestion but it didn't work. Crashed at the same spot, which is "If Not rst.BOF() then..." with the same error messge.

Lisa, I am going tried...

Another version of code. This one is using command.

Sub Test()

  Dim RangeA As Range

  Dim RangeB As Range

  Set RangeA = Worksheets("CS").Range("B3")

  Set RangeB = Worksheets("CS").Range("G3")

  Dim cmd As...

exec proc_Charge2 will return results in QA.

Below is my revision for the sub proc in Excel:

Sub Test()

Dim RangeA As Range

Dim RangeB As Range

Set RangeA...

almost to that point...i made a change to my stored procedure and see if that any diff. Just to test until I figure out why it bombed. I removed the...

I also set a breakpoint right after the statement "Set rst = cmd.Execute". The rst.fields.count = 0 when I checked the count value for the recordset on the immediate window.

ok, cje. The istatus return a value of 1.

Lisa, I used the MS Component Checker to check my MDAC version. It reports that I have MDAC 2.8 SP1 on my PC.