none
Import Access data into Excel using excel parameter RRS feed

  • Question

  • Hi All,

    I am using below code and query in excel to connect to access database and run sql query by providing a parameter in my code but query is returning nothing except headers.

    Sub ImpData1()
    Dim cn As Object
    Dim rs As Object
    Dim strFile As String
    Dim strCon As String
    Dim strSQL, strInput As String
    
    strFile = "C:DB.accdb"
    
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strFile
    
    Set cn = CreateObject("ADODB.Connection")
    cn.Open strCon
    
    strInput = "JOHN SMITH"
    
    
    
    strSQL = "SELECT [MYQUERY_1].* FROM [MYQUERY_1] WHERE [MYQUERY_1].PERSONS_Full_Name=""strInput "";"
    Debug.Print strSQL
    
    
    'Added the following four lines
    Set rs = CreateObject("ADODB.RECORDSET")
    rs.ActiveConnection = cn
    rs.Open strSQL
    
    
    
    With ThisWorkbook.Sheets("Sheet1")
      For i = 1 To rs.Fields.Count
        .Cells(1, i).Value = rs.Fields(i - 1).Name 'fields is a 0 based collection
      Next i
      .Range("A2").CopyFromRecordset rs
    End With
    
      rs.Close
      cn.Close
    Set cn = Nothing
    
    End Sub

    If i provide value in where  clause as per below query than sql query runs properly

    strSQL = "SELECT [MYQUERY_1].* FROM [MYQUERY_1] WHERE [MYQUERY_1].PERSONS_Full_Name=""JOHN SMITH "";"

    How shall i put double quotes in my first query for strInput?

    Thanks,

    Zav

    Sunday, September 13, 2015 10:15 PM

Answers

  • >>>How shall i put double quotes in my first query for strInput?

    You should include single quotation marks in the criteria argument in such a way that when the value of the variable is concatenated into the string, it will be enclosed within the single quotation marks. For instance, suppose your criteria argument must contain a string variable called

    strName

    You could construct the criteria argument as in the following example:

    "[LastName] = '" & strName & "'"

    When the variable strName is evaluated and concatenated into the criteria string, the criteria string becomes:

    "[LastName] = 'Smith'"

    So you could modify your code refer to below:

    'strSQL = "SELECT [MYQUERY_1].* FROM [MYQUERY_1] WHERE [MYQUERY_1].PERSONS_Full_Name=""strInput "";"
    
    "SELECT [MYQUERY_1].* FROM [MYQUERY_1] WHERE [MYQUERY_1].PERSONS_Full_Name='" & strInput & "';"
    

    For more information, click here to refer about How to: Include Quotation Marks in String Expressions

    • Marked as answer by zaveri cc Monday, September 14, 2015 1:21 PM
    Monday, September 14, 2015 8:43 AM

All replies

  • >>>How shall i put double quotes in my first query for strInput?

    You should include single quotation marks in the criteria argument in such a way that when the value of the variable is concatenated into the string, it will be enclosed within the single quotation marks. For instance, suppose your criteria argument must contain a string variable called

    strName

    You could construct the criteria argument as in the following example:

    "[LastName] = '" & strName & "'"

    When the variable strName is evaluated and concatenated into the criteria string, the criteria string becomes:

    "[LastName] = 'Smith'"

    So you could modify your code refer to below:

    'strSQL = "SELECT [MYQUERY_1].* FROM [MYQUERY_1] WHERE [MYQUERY_1].PERSONS_Full_Name=""strInput "";"
    
    "SELECT [MYQUERY_1].* FROM [MYQUERY_1] WHERE [MYQUERY_1].PERSONS_Full_Name='" & strInput & "';"
    

    For more information, click here to refer about How to: Include Quotation Marks in String Expressions

    • Marked as answer by zaveri cc Monday, September 14, 2015 1:21 PM
    Monday, September 14, 2015 8:43 AM
  • Thanks David, The solution works.
    Monday, September 14, 2015 1:21 PM