cannot open recordset in vba

Question

When this vba code tries to open up the recordset, I get the following error: Run Time Error '3709' The connection cannot be used to perform this operation. It is either closed or invalid in this context.

Set objMyConn = New ADODB.Connection
Set objMyRecordset = New ADODB.Recordset
Dim strSQL As String

objMyConn.ConnectionString = "Driver={SQL Server};Server=localhost\SQLEXPRESS;Database=Contact;Trusted_Connection=True;"
objMyConn.Open

strSQL = "Select * from Contact where Lastname like " + Chr(39) + LastSearch + "%" + Chr(39) + " And Firstname like " + Chr(39) + FirstSearch + "%" + Chr(39)

MsgBox strSQL

objMyRecordset.Open strSQL, cnn, adOpenForwardOnly, adLockOptimistic

Answer 1

Add Option Explicit at the top of your module; you'll find the VBE screaming at that undeclared cnn variable.

Your recordset isn't using any open connection - as the error message is saying.

That said you can very well have single quotes inside the string literals; that Chr(39) stuff is just uselessly obfuscating the code.

Also consider using parameters instead. If you're not sure why, read about Little Bobby Tables.

---

Here's an example:

Option Explicit

Sub Test()
    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection

    conn.ConnectionString = "Provider='SQLOLEDB';Data Source='INSTANCE NAME';Initial Catalog='DATABASE NAME';Integrated Security='SSPI';"
    conn.Open

    Dim sql As String
    sql = "SELECT Field1 FROM dbo.TestTable WHERE Field3 LIKE '%' + ? + '%'"

    Dim results As ADODB.Recordset
    With New ADODB.Command
        .ActiveConnection = conn
        .CommandType = adCmdText
        .CommandText = sql
        .Parameters.Append .CreateParameter(Type:=adVarChar, Value:="foo", Size:=255)

        Set results = .Execute

    End With

    Debug.Print results(0).Name, results(0).Value

    results.Close
    conn.Close
End Sub

Notice it's the Command that executes off the Connection and returns a Recordset.