VBA_Coder VBA_Coder - 1 year ago 117
SQL Question

Select SQL Statement in Excel VBA

Sub LogCheck()
Dim cn As Object
Dim rs As Object
Dim StrSql As String
Dim strConnection As String
Dim AppPath As String
Set cn = CreateObject("ADODB.Connection")
AppPath = Application.ActiveWorkbook.Path
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\ceo.accdb;"
cn.Open strConnection
S_ID = Sheets("My").Range("A1").Value
StrSql = "SELECT * FROM EDO Where ID = ' " & S_ID & " '"
rs.Open StrSql, cn
If rs = Null Then
MsgBox "Record Not found"
MsgBox "Record Found"
End If
End Sub

I am unable to run this code. Its showing error. Please help me out. Thanks!
is the data which I would like to search from table & ID is the primary key in the EDO Table.

Answer Source

In this case you may detect if the recordset is empty checking .EOF property:

Sub TestIfRecordFound()

    Dim strConnection As String
    Dim strID As String
    Dim strQuery As String
    Dim objConnection As Object
    Dim objRecordSet As Object

    strConnection = _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source='C:\ceo.accdb';"
    strID = Sheets("My").Range("A1").Value
    strQuery = _
        "SELECT * FROM EDO WHERE ID = '" & strID & "';"

    Set objConnection = CreateObject("ADODB.Connection")
    objConnection.Open strConnection
    Set objRecordSet = objConnection.Execute(strQuery)

    If objRecordSet.EOF Then
        MsgBox "Record Not found"
        MsgBox "Record Found"
    End If


End Sub
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download