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

What is cnn here? Shouldn't that be objMyConn ?

Tim Williams
– Tim Williams

2017-08-31 00:58:13 +00:00
Commented Aug 31, 2017 at 0:58 — Tim Williams
– Tim Williams, Commented Aug 31, 2017 at 0:58

Mathieu Guindon · Accepted Answer · 2017-08-31 02:02:16Z

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.

edited Aug 31, 2017 at 2:02

answered Aug 31, 2017 at 1:22

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

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

3 Comments

Luv Over a year ago

thanks. surprised i didn't see that. i had option explicit but i didn't get variable declaration complaint....

PP8 Over a year ago

@Mathieu Guindon can the SQL statement be replaced by running an SQL stored procedure by command type and command text?

Mathieu Guindon Over a year ago

@Jose absolutely! The command text is then just the SP name, and parameters are ordinal (append order matters)

Doug Coats · Accepted Answer · 2017-08-31 00:58:11Z

0

Here's generic ADODB connection set up

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strConnection As String

strConnection = "connectionString"
con.Open strConnection
rs.Open "SELECT * FROM Tbl", con

answered Aug 31, 2017 at 0:58

Doug Coats

7,2279 gold badges32 silver badges56 bronze badges

Collectives™ on Stack Overflow

cannot open recordset in vba

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related