none
Calling Stored Proc's in Excel VBA after Win7 SP1 RRS feed

  • Question

  • I was using ADO 2.8 to call MS SQL stored proc's from Excel VBA.  Win7 SP1 has put an end to this ability.  I am running unpatched for now.  Is there a work around?  Here is an example of what I was using successfully.  I tried the late binding work around without success.  I would get errors on the Set statements.

    Thanks

     

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim fld As Field

    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset

    reccount = 0
    CustID$ = "12345"
       
    cnn.Open "Database$;"
    rst.ActiveConnection = cnn
    rst.CursorLocation = adUseServer
     
    rst.Source = "exec SelectProfile @CustID = '" & CStr(CustID$) & "'"
    rst.Open
     
    For Each fld In rst.Fields
      'Get record count from stored proc
      reccount = fld.Value
    Next

    cnn.Close

    Monday, July 11, 2011 3:26 PM

Answers

  • Tony/Peter,

    Many thanks for helping out a novice in this area.  For the record the following code worked for me in an Excel macro.

    Dim cnn As Object    'ADODB.Connection
    Dim rst As Object     'ADODB.Recordset
    Dim fld As Object  'Field

    Dim reccount

    Set cnn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")

    reccount = 0
       
    cnn.Open "(Insert ODBC DSN here);"
    rst.ActiveConnection = cnn
     
    rst.Source = "exec SelectProfile @CustID = '12345'"
    rst.Open
     
    For Each fld In rst.Fields
      'Get record count from stored proc
      reccount = fld.Value
    Next

    cnn.Close



    • Marked as answer by LongEZ Tuesday, July 12, 2011 5:06 PM
    Tuesday, July 12, 2011 4:29 PM

All replies

  • I can't speak for the actual problem, but what errors do you get, exactly, on the Sets using Late Binding?
     

    Enjoy,
    Tony
    www.WordArticles.com
    Monday, July 11, 2011 3:33 PM
  • What does "to call MS SQL stored proc's from Excel VBA" mean. What does "running unpatched mean". When you say "Win7 SP1 has put an end to this ability", what ability are you referring to.

    There is indeed a capability issue with the new ADO 2.8 dll shipped with W7 SP1 if compiled with the app and distributed to users running earlier versions, is that what you re talking about. If so, convert your app to Late Binding to avoid such issues, now and in the future.

    Peter Thornton

    Monday, July 11, 2011 3:42 PM
    Moderator
  • Tony,

    In the above code I get "Complie error, User - defined type not defined" on the the first Set statement.

    Set cnn = New ADODB.Connection

    Tuesday, July 12, 2011 3:32 PM
  • Peter,

    I am using VBA in the context of an Excel Macro.  I have a number of MS SQL stored procedures in a database that I can call from  Excel VBA macro code and are used by other non-VBA applications.  I may call up to a dozen of these in my Excel application.

    Running unpatched means that I backed out Win7 SP1 in as soon as I discovered the problem in order to continue to be able to call the stored procs.  I would be happy to change to late binding, but all I know about it is what was posted in the response to another report of this problem.  If you could point me to a resource, it would be much appreciated. The above code is my attempt to head down that path with a stripped down test macro.

    Tuesday, July 12, 2011 3:52 PM
  • With Late Binding you can't reference the Object like that (becuase it's not yet Bound). Use, instead, ..
     
        Set cnn = CreateObject("ADODB.Connection")
     

    Enjoy,
    Tony
    www.WordArticles.com
    Tuesday, July 12, 2011 3:56 PM
  • Tony/Peter,

    Many thanks for helping out a novice in this area.  For the record the following code worked for me in an Excel macro.

    Dim cnn As Object    'ADODB.Connection
    Dim rst As Object     'ADODB.Recordset
    Dim fld As Object  'Field

    Dim reccount

    Set cnn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")

    reccount = 0
       
    cnn.Open "(Insert ODBC DSN here);"
    rst.ActiveConnection = cnn
     
    rst.Source = "exec SelectProfile @CustID = '12345'"
    rst.Open
     
    For Each fld In rst.Fields
      'Get record count from stored proc
      reccount = fld.Value
    Next

    cnn.Close



    • Marked as answer by LongEZ Tuesday, July 12, 2011 5:06 PM
    Tuesday, July 12, 2011 4:29 PM
  • I would be happy to change to late binding, but all I know about it is what was posted in the response to another report of this problem. If you could point me to a resource, it would be much appreciated.

    Copied from a recent related reply -

    Change all object declarations that refer to the library to As Object, eg
    Dim con As Object ' ADODB.Connection
    Dim recSet As Object  ' ADODB.Recordset

    Change any use of "New" to
    Set con = CreateObject("ADODB.Connection")

    Add module level constants for all named constants from the library that your code uses, eg
    Private Const adLockReadOnly As Long = 1& ' or Public if used in multiple modules
    (look up their intrinsic values up in F2 Object Browser or return them in the Immediate window)

    Head all modules Option Explicit

    Remove (untick) the ADO reference

    On the VBE menu do Debug / Compile
    Correct anything that fails to compile

    Test!

    Changing to Late Binding means you will lose Intellisense and performance might be a tad slower (typically not noticeable). However you will (normally) avoid compatibility issues. For further development add the reference back and fully declare the object variables (no need to change CreateObject back to New)

    Peter Thornton

    Tuesday, July 12, 2011 6:30 PM
    Moderator