Select SQL Statement in Excel VBA

Question

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"
    Else
        MsgBox "Record Found"
    End If
End Sub

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

there probably should not be spaces between the ' and the ". like this: "SELECT * FROM EDO Where ID = '" & S_ID & "'" unless your data has a preceding and trailing space. — Scott Craner
– Scott Craner, Commented Jan 7, 2016 at 18:18
Run Time Error 91 Object variable or With block variable not set — Developer_World
– Developer_World, Commented Jan 7, 2016 at 18:36
try declaring rs as recordset. Dim rs As New ADODB.Recordset — Scott Craner
– Scott Craner, Commented Jan 7, 2016 at 18:40

omegastripes · Accepted Answer · 2016-01-07 19:17:10Z

0

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"
    Else
        MsgBox "Record Found"
    End If

    objConnection.Close

End Sub

answered Jan 7, 2016 at 19:17

omegastripes

12.6k4 gold badges52 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

PKatona · Accepted Answer · 2016-01-07 18:27:08Z

0

If Id is numeric than the sql should be:

StrSql = "SELECT * FROM EDO WHERE Id = " & S_ID

You also did not define S_ID, so it will be handle as a variant here. If you still get an error, you might have to make it "& CStr(S_ID)".

answered Jan 7, 2016 at 18:27

PKatona

6492 gold badges9 silver badges22 bronze badges

6 Comments

Developer_World Over a year ago

Here wdate is current date & W_Date is the field in the table with Date/Time data type. but its showing record not found as there are 10 records existing in the table. StrSql = "SELECT * FROM ceo WHERE wDate = " & CDate(W_Date) ' If objRecordSet.EOF Then MsgBox "Record Not found" Else MsgBox "Record Found" End If'

PKatona Over a year ago

Date fields in an access DB contain date and time, so your select will not work unless all dates were stored with just the date. You probably need to modify you select as follows: "SELECT * FROM ceo WHERE " & CDate(Format(wDate,"DD/MM/YYYY")) & " = " & CDate(W_Date). You might also have to convert both sides of the equal sign to string by using CStr.

Developer_World Over a year ago

Its showing error type mismatch. StrSql = "SELECT * FROM ceo WHERE " & CDate(Format(wDate, "MM/DD/YYYY")) & " = " & CDate(W_Date)

PKatona Over a year ago

Try: "SELECT * FROM ceo WHERE #" & CStr(CDate(Format(wDate, "MM/DD/YYYY"))) & "# = #" & CStr(CDate(W_Date))) & "#"

Developer_World Over a year ago

This one also showing same error. May I store date as string in database table.

|

Collectives™ on Stack Overflow

Select SQL Statement in Excel VBA

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related