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
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
.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments