locked
VBA, Access, & SQL Server RRS feed

  • Question

  • 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

    Monday, March 27, 2006 6:26 PM

Answers

  • Per our support engineer:

    After reviewing the case log, I understand that the long-duration stored procedure executes fine in Query Analyzer, but terminates early in Access VBA (about 3 minutes later).
     
    First, I should that executing a long-duration stored procedure in code is not a good idea. It can cause your UI to freeze and users do not know what is going on. If possible, create a job to execute the stored procedure (you can execute the job periodically).
     
    If you really want to execute in the code, you need to set timeout for the command, so that long-duration stored procedure will not time out.
     
     
    For ADO sample to execute a stored procedure, you can refer to the following article:

    -brenda (ISV Buddy Team)

    Tuesday, April 4, 2006 9:57 PM