VBA, Access, & SQL Server

Access 2003, SQL Server 8

Hello,

I'm having some problems calling a stored procedure from Access VBA. The stored proc is rather large, calls a few other procedures, and takes about two hours to execute from Query Analyzer (it's the second EXEC statement listed below).

The problem I have is calling it from VBA. I've used ADO and DAO; but no matter what I use the SP terminates early (like after 3 minutes) and doesn't do what it should. When I run it from query analyzer, it completes as it should (albeit two hours later). For the life of me I can't figure out why the stored proc will execute fine from Query Analyzer but not when called from VBA.

Any help or ideas would be GREATLY appreciated!!!

-Jason

' Open ADO connection
'strCnxn = "Provider='sqloledb';Data Source='SDSQL2';" & _
' "Initial Catalog='TimberlineWarehouse';Integrated Security='SSPI';"
'Set CnXn = New ADODB.Connection
'CnXn.ConnectionTimeout = 0
'CnXn.Open strCnxn

'Execute stored procedures CorporateAuditFootnote for Timberline & Yardi
'CnXn.Execute "EXEC dbo.CorporateAuditFootnoteInitialize_UnconsolidatedYardi"
'CnXn.Execute "EXEC dbo.CorporateAuditFootnote_UnconsolidatedYardi '" & FiscalYear & "'"

'Close ADO connection
'CnXn.Close

' Open DAO
Set WrkSpc = CreateWorkspace("", "admin", "", dbUseODBC)
Set dbs = WrkSpc.OpenDatabase("TimberlineWarehouse", , True, "ODBC;Driver={SQL Server};Server=SDSQL2;Database=TimberlineWarehouse;Trusted_Connection=Yes")

dbs.Execute "EXEC dbo.CorporateAuditFootnoteInitialize_UnconsolidatedYardi"
dbs.Execute "EXEC dbo.CorporateAuditFootnote_UnconsolidatedYardi '" & FiscalYear & "'"

'Close DAO
dbs.Close
Set dbs = Nothing