Running Store Procedure from VBA Excel returns 0 rows (although data exist)

Dear All,

Below is written in VBA with a purpose to retrieve the data out to spread sheet from a stored procedure in Sybase.

The database and user name has been all setup OKAY both in the ODBC as well as in all variables inside the macro, however when it comes to executing this section from the macro vba code -----> ls_sql = "exec transaction..pGetData 'HH','20080815','N'" <-- it returns no data at all - although using the same user name and database, I am able to get the data out from another database tool with exactly the same command.

For another testing , I have no issue to get the data out to the excel when using normal select statement ---> ' ls_sql = "select * from master..Customer_tab".

This user name (macro_user) has been granted 'Execute' access as well for the store procs so there should be no reason why it returns no rows with EOF and BOF is TRUE ( header titles are returned OK ). also it works fine using the same user name when querying from another data query tools ( dbArtisan, isql )

Would appreciate anyone's advise on this, Thanks
=======================
Const kAaa = "contract_db"
Const kAaaAcct = "macro_user"
Const kAaaPass = "macro_user"

Set dbAaa = OpenDatabase(kAaa, dbDriverNoPrompt, True, _
   "ODBC;DSN=" & kAaa & ";UID=" & kAaaAcct & ";PWD=" & kAaaPass)
   
Dim ls_sql As String, dtTemp

ls_sql = "exec transaction..pGetData 'HH','20080815','N'" <-- RETURNS NO DATA AT ALL
' ls_sql = "select * from master..Customer_tab" <--- this one has no issue at all, it returns data as expected

' Get record set
Set rsSet = dbAaa.OpenRecordset(ls_sql, dbOpenForwardOnly, dbSQLPassThrough)
 
' Loading Label
For nIdx = 1 To rsSet.Fields.Count
  ActiveSheet.Cells(1, nIdx).Value = rsSet.Fields(nIdx - 1).Name
Next

' Loading query result
ActiveSheet.Range("A2").CopyFromRecordset rsSet

rsSet.Close
=======================

Start Free Trial