Invalid SQL Statement Error When Applying Filter

[G. Ray Giacalone]

An ADO Recordset is created and then set to my subform in an mdb MS Access
file:

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection

With cnn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = "SQLDB"
.Properties("User ID").Value = CurUserAcct
.Properties("Password").Value = CurUserPswrd
.Properties("Initial Catalog").Value = CurDBName
.Open
End With

Set rst = New ADODB.Recordset

With rst
Set .ActiveConnection = cnn
.Source = strSQL
.LockType = adLockOptimistic
.CursorType = adOpenStatic
.Open
End With

Set
Forms!frmBulkContractEdits2.Controls!sfrmBulkContractEdits2.Form.Recordset =
rst

rst.Close
Set rst = Nothing
Set cnn = Nothing

However, when applying a filter using the built-in filter feature included
in the subform (accessed through the right-click context menu) I get the
following error message:

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE','SELECT', or
'UPDATE'

This will pop up three times before the filter is successfully applied...do
you know why this happens and how this can be fixed?

--Thanx!

 

[Douglas J. Steele]

What's the value of strSQL?

 

[G. Ray Giacalone]

Hi Doug!

I am executing a stored proc:

EXEC [dbo].[VP_CustomerInput_BulkEdit_Get] @sAMAccountName = 'rgiacalone',
@CustomerID = 'MYCOMPANY2'

 

[Douglas J. Steele]

I don't use ADO in the way you are. To show that it's a stored procedure, I
pass adCmdStoredProc as the Option parameter in the Open statement. However,
I don't think you can just use .Option = adCmdStoredProc: I usually use the

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

syntax.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug!

I am executing a stored proc:

EXEC [dbo].[VP_CustomerInput_BulkEdit_Get] @sAMAccountName = 'rgiacalone',
@CustomerID = 'MYCOMPANY2'

What's the value of strSQL?