none
Using Parameters in Excel Connection Properties with SQLOLEDB Provider

    Question

  • It appears that the Connection Properties in Excel does not recognize parameters (identified as ? - question marks in the SQL statement) when using SQLOLEDB Provider connecting to a MS SQL Server 2005 database.

    Is there a workaround, besides actually entering the connection definition in a VBA macro?

    • Moved by Larcolais Gong Monday, August 15, 2011 7:58 AM (From:ADO.NET Managed Providers)
    Thursday, August 11, 2011 3:20 PM

Answers

  • Hello,

    AFAIK, I made a test on my side. I recorded the macro on my side. Please check it.

    Sub Macro1()
    
    '
    
    ' Macro1 Macro
    
    '
    
    
    
    '
    
     With ActiveWorkbook.Connections("Query from test").ODBCConnection
    
      .BackgroundQuery = True
    
      .CommandText = Array( _
    
      "SELECT Address.AddressID, Address.City, Address.PostalCode" & Chr(13) & "" & Chr(10) & "FROM AdventureWorks.Person.Address Address" & Chr(13) & "" & Chr(10) & "WHERE City " _
    
      , "= ?")
    
      .CommandType = xlCmdSql
    
      .Connection = Array(Array( _
    
      "ODBC;DRIVER=SQL Server;SERVER=VICKY03-MSFT;UID=v-xugong;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=V-XUGONG-MSFT;DATABASE" _
    
      ), Array("=AdventureWorks"))
    
      .RefreshOnFileOpen = False
    
      .SavePassword = False
    
      .SourceConnectionFile = ""
    
      .SourceDataFile = ""
    
      .ServerCredentialsMethod = xlCredentialsMethodIntegrated
    
      .AlwaysUseConnectionFile = False
    
     End With
    
     With ActiveWorkbook.Connections("Query from test")
    
      .Name = "Query from test"
    
      .Description = ""
    
     End With
    
     ActiveWorkbook.Connections("Query from test").Refresh
    
    End Sub
    
    
    
    

    When I filter the result, it forced me to provide the parameter value and pass it into sql statement explicitly. If not, it cannot be executed. In addition, I found the following KB about ADO.NET to retrieve and modify records in Excel. It used "?" symbol, but also explicitly use Parameter.Add method to declare the parameters. I think this step was similar with adding the parameter in the Excel. Check here for more information. http://support.microsoft.com/kb/316934

    Plus, I moved your case into Excel forum for further support.

    Hope this helps.

    Thanks,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    • Marked as answer by Bruce Song Wednesday, August 24, 2011 3:09 AM
    Monday, August 15, 2011 7:58 AM

All replies

  • Sounds like an Excel question. I believe that in order to use parameters you need to construct your query using MSQuery and an ODBC data source (Data...From Other Sources...From Microsoft Query).

    You may want to post this question in the Excel for Developers forum if you need more info (or perhaps a moderator can move it there).

    http://social.msdn.microsoft.com/Forums/en/exceldev/threads

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, August 12, 2011 1:40 PM
  • The problem is that if I use OLEDB providers for other databases (notably DB2 and Oracle), I don't have that problem. They allow parameters without any issues, hence I thought it had more to do with the OLEDB provider for MS SQL Server rather than Excel application.

    I'll cross-post to Excel forums, per your suggestion, just in case if someone there might have some ideas on how to circumvent this silly behaviour.

     

    Thank you,

    Sergey

    Friday, August 12, 2011 2:40 PM
  • Admittedly I'm not that familiar with the Excel environment, but I wasn't able to add parameters for an Oracle data source either - at least not through the Data Connection Wizard.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, August 12, 2011 3:06 PM
  • Hello,

    AFAIK, I made a test on my side. I recorded the macro on my side. Please check it.

    Sub Macro1()
    
    '
    
    ' Macro1 Macro
    
    '
    
    
    
    '
    
     With ActiveWorkbook.Connections("Query from test").ODBCConnection
    
      .BackgroundQuery = True
    
      .CommandText = Array( _
    
      "SELECT Address.AddressID, Address.City, Address.PostalCode" & Chr(13) & "" & Chr(10) & "FROM AdventureWorks.Person.Address Address" & Chr(13) & "" & Chr(10) & "WHERE City " _
    
      , "= ?")
    
      .CommandType = xlCmdSql
    
      .Connection = Array(Array( _
    
      "ODBC;DRIVER=SQL Server;SERVER=VICKY03-MSFT;UID=v-xugong;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=V-XUGONG-MSFT;DATABASE" _
    
      ), Array("=AdventureWorks"))
    
      .RefreshOnFileOpen = False
    
      .SavePassword = False
    
      .SourceConnectionFile = ""
    
      .SourceDataFile = ""
    
      .ServerCredentialsMethod = xlCredentialsMethodIntegrated
    
      .AlwaysUseConnectionFile = False
    
     End With
    
     With ActiveWorkbook.Connections("Query from test")
    
      .Name = "Query from test"
    
      .Description = ""
    
     End With
    
     ActiveWorkbook.Connections("Query from test").Refresh
    
    End Sub
    
    
    
    

    When I filter the result, it forced me to provide the parameter value and pass it into sql statement explicitly. If not, it cannot be executed. In addition, I found the following KB about ADO.NET to retrieve and modify records in Excel. It used "?" symbol, but also explicitly use Parameter.Add method to declare the parameters. I think this step was similar with adding the parameter in the Excel. Check here for more information. http://support.microsoft.com/kb/316934

    Plus, I moved your case into Excel forum for further support.

    Hope this helps.

    Thanks,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    • Marked as answer by Bruce Song Wednesday, August 24, 2011 3:09 AM
    Monday, August 15, 2011 7:58 AM