how to retrieve data from access to excel using vba?

Question

I am trying to retrieve data from access using excel-vba but the problem is when i want to use an AND in SQL query it throws an error.

This works

SQL = "SELECT " & col_name & " FROM PhoneList WHERE Item LIKE '" & var & "%" & "'"

This doesn't

SQL = "SELECT " & col_name & " FROM PhoneList WHERE Item LIKE '" & item & "%" & "'" & " AND Size Like '" & size & "%" & "'"

even this doesn't work

SQL = "SELECT Standard FROM PhoneList WHERE Item = 'Shank Button' AND Size = '17'"

This throws an

error -2147467259 Method 'open'object' _recordset' failed

from excel where it works fine if I try it in access query

SELECT Standard FROM PhoneList WHERE Item = 'Shank Button' AND Size = '17'

here is my table [PhoneList] structure

ID | Item | Size | Standard | Customized Standard | Premium | Customized Premium

here, the combination of Item and Size makes a record unique.

I assume only AND part is creating the problem or something is wrong with recordset pasting to the excel sheet.

I am trying to retrieve only one value from DB where Item, size and category type (Standard or Customized Standard or Premium) will be taken from excel userform's combobox, and I will show that value in a cell or in a textfield.

Any help will be highly appreciated.

here is my code ...

 Option Explicit
Private Sub CommandButton1_Click()
    'Declaring the necessary variables.
    Dim cnn As ADODB.Connection 'dim the ADO collection class
    Dim rs As ADODB.Recordset 'dim the ADO recordset class
    Dim dbPath As String
    Dim SQL As String
    Dim i As Integer
    Dim item As String

    'add error handling
    On Error GoTo errHandler:

    'Disable screen flickering.
    Application.ScreenUpdating = False
    'Clear the old data
    Sheet2.Range("A2:G10000").ClearContents

    'Variables
    dbPath = Sheet1.Range("I3").Value
    item = ComboBox1.Text

    'Initialise the collection class variable
    Set cnn = New ADODB.Connection

    'Connection class is equipped with a —method— named Open
    '—-4 aguments—- ConnectionString, UserID, Password, Options
    'ConnectionString formula—-Key1=Value1;Key2=Value2;Key_n=Value_n;
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

    'Create the SQL statement to retrieve the data from table.
    Dim col_name As String
    Dim Isize As String

    Isize = "17"
    col_name = ComboBox3.Text

    If Sheet2.Range("J2").Value = "Yes" Then
        SQL = "SELECT * FROM PhoneList WHERE Item = '" & item & "'"
        Else
        SQL = "SELECT " & col_name & " FROM PhoneList WHERE Item LIKE '" & item & "%" & "'" & " AND Size LIKE '" & Isize & "%" & "'"
        'SQL = "SELECT " & col_name & " FROM PhoneList t WHERE t.Item LIKE '" & item & "*' AND t.Size = " & size
    End If

    'Create the ADODB recordset object.
    Set rs = New ADODB.Recordset 'assign memory to the recordset

    'ConnectionString Open '—-5 aguments—-
    'Source, ActiveConnection, CursorType, LockType, Options
    rs.Open SQL, cnn

    'Check if the recordset is empty.
    If rs.EOF And rs.BOF Then
        'Close the recordet and the connection.
        rs.Close
        cnn.Close
        'clear memory
        Set rs = Nothing
        Set cnn = Nothing
        'Enable the screen.
        Application.ScreenUpdating = True
        'In case of an empty recordset display an error.
        MsgBox "There are no records in the recordset!", vbCritical, "No Records"
        Exit Sub
    End If

    'Write the reocrdset values in the sheet.
    Sheet2.Range("a2").CopyFromRecordset rs

    'Close the recordset and the connection.
    rs.Close
    cnn.Close
    'clear memory
    Set rs = Nothing
    Set cnn = Nothing

    'Update the worksheet
    Application.ScreenUpdating = True

    'Inform the user that the macro was executed successfully.
    MsgBox "Congratulation the data has been successfully Imported", vbInformation, "Data Imported"
    On Error GoTo 0
    Exit Sub
errHandler:
    'clear memory
    Set rs = Nothing
    Set cnn = Nothing
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Import_Data"

End Sub

Thanks

Answer 1

There are a few issues here -

1. MS Access uses the asterisk * as the wildcard character to represent any sequence of characters (or no characters), not the percent symbol % used by some other RDBMS.

2. size is a reserved word in MS Access and so could potentially cause problems unless enclosed in square brackets or prefixed with the table qualifier.

3. Assuming the Size field is numerical (as the name would imply), you should not surround the value with single quotes, hence the code might become:

   SQL = "SELECT " & col_name & " FROM PhoneList t WHERE t.Item LIKE '" & item & "*' AND t.Size = " & size
   

Answer 2

There should be a space before the AND:

SQL = "SELECT " & col_name & " FROM PhoneList WHERE Item LIKE '" & item & "%" & "'" & " AND Size Like '" & item & "%" & "'"