Access Call Stored Procedure with Pass Through Parameter

Question

I'm using a Access 2010 front-end linked to a SQL Server 2012 database back-end.

In the Access frontend I have a general search screen, consisting of a text box whereby the results are displayed in a listbox. I want the text box to be able to search multiple fields that will also need to be wild cards.

So, my stored procedure is this:

ALTER PROCEDURE [dbo].[SalesGeneralSearch]
   @Search nvarchar(50) = ''
AS
BEGIN
   SET NOCOUNT ON;

   SELECT 
      tblJobHead.JobNum, 
      tblCustomer.LastName, 
      tblCustomer.M3DealerCode, 
      tblCustomer.TradeRef, 
      tblCustomer.Postcode, 
      tblJobHead.[Item Number], 
      tblJobHead.Description, 
      tblStatus.[Desc] AS Status
   FROM 
      tblCustomer 
   INNER JOIN 
      (tblJobHead 
   INNER JOIN 
      tblStatus ON tblJobHead.Status = tblStatus.StatusID) ON tblCustomer.RepNum = tblJobHead.[Rep Num]
   WHERE 
      (((tblJobHead.JobNum) Like '%'+ @Search + '%')) 
       OR (((tblCustomer.LastName) Like '%' + @Search + '%')) 
       OR (((tblCustomer.M3DealerCode) Like '%' + @Search + '%')) 
       OR (((tblCustomer.TradeRef) Like '%' + @Search + '%')) 
       OR (((tblCustomer.Postcode) Like '%' + @Search + '%'))
   ORDER BY 
      tblJobHead.JobNum DESC;
END

Ok, so now back to Access. On the search screen, I have textbox where the user inputs, a button to click to search and a listbox for the results. On the click event of the button I have the following code:

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim adString As Variant


Set cn = New ADODB.Connection

cn.ConnectionString = "DRIVER=SQL Server;SERVER=XXX;Database=XXX;Trusted_Connection=YES;"
cn.Open

    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandText = "dbo.SalesGeneralSearch"
        .CommandType = adCmdStoredProc
        Set prm = .CreateParameter("@Search", adString, adParamInput)
        .Parameters.Append prm
        cmd.Execute
        prm.Value = Me.Search.Text
    End With

    Set rs = New ADODB.Recordset
    With rs
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockReadOnly
        .Open cmd
    End With
    Set Me!lstJobQuickSearch.Recordset = rs
    Me.lstJobQuickSearch.Requery
Set prm = Nothing
Set cmd = Nothing

However, when I click on the search button I keep receiving the below error:

runtime error 3708 parameter object improperly defined

and it highlights

.Parameters.Append prm

The row source of the lstJobQuickSearch is a pass through query as well using the following:

SELECT tblJobHead.JobNum, tblCustomer.LastName, tblCustomer.M3DealerCode, tblCustomer.TradeRef, tblCustomer.Postcode, tblJobHead.[Item Number], tblJobHead.Description, tblJobHead.FN, tblStatus.[Desc] AS Status
FROM tblCustomer INNER JOIN (tblJobHead INNER JOIN tblStatus ON tblJobHead.Status = tblStatus.StatusID) ON tblCustomer.RepNum = tblJobHead.[Rep Num]
ORDER BY tblJobHead.JobNum DESC;

Where have I gone wrong? Any help would be greatly appreciated.

As I just want to return the values, is it more efficient just to use a pass through query and code the parameters? I'm not sure, I've been working on this for days : -(

Regards,

Michael

Solution 1

The issue is that the stored procedure declares an input parameter as nvarchar(50) but in VBA the ADODB.Parameter was being defined using adString and no length. Problems:

1. nvarchar on SQL Server maps to adVarWChar in ADO
2. string parameters almost always need to have a defined (maximum) length

So the fix is to change the parameter declaration to

Set prm = .CreateParameter("@Search", adVarWChar, adParamInput, 50)

Solution 2

I would use a DAO pass-though. Assuming you saved a pass-though query, then this code will work:

  With CurrentDb.QueryDefs("qPass")
     .SQL = "exec SalesGeneralSearch " & strSearch
     Set Me.MyListBox.RowSource = .OpenRecordset
  End If

There really no need for these massive whacks of code posted which only serves as theft of company billable hours by dishonest developers when a simple 2 lines as per above will suffice.

Comments

[Gord Thompson]

Try Set prm = .CreateParameter("@Search", adVarWChar, adParamInput, 50)

[Michael]

right, thanks for the explanation, that makes sense now. Thanks again.

[Gord Thompson]

It strikes me as rather unfair to categorize the use of a properly parameterized ADO approach as "theft of company billable hours by dishonest developers". At the very least your example would need to make some effort to delimit the input parameter in case it contains one or more spaces, and then it would need to escape any delimiters within the user input to avoid potential syntax errors inherent in dynamic SQL. Sometimes we need to choose between between "doing it fast" and "doing it right".

[Albert D. Kallal]

That’s a fair point. No complains if solution is 10, 15 or even 25 LOC. The example has in excess of 30 LOC. My example has 4. So sure include documentation, include white space for increased readability etc. ALL these things are great and it would be SILLY of me to suggest less code is always better. I MUCH prefer extra lines of code to improve readability and maintains. Jumping from 4 LOC to OVER 30 lines of code? Well then yes some LEGITIMATE questions arise and start needing to be asked. Such ratios of 9X means for my 15 LOC some will post 135 and such ratios warrant scrutiny.

[Michael]

I only started coding 3 months ago - give me a break!

[Albert D. Kallal]

My response was not meant to offend you. In fact my comment was not towards you anyway and was a follow up to Gord’s point. Gord was MOST FAIR to point out and comment that using ADO is not “theft”. In fact it is not, nor did I suggest as such. However I also stand by my simple point WHEN posted solutions start to reach 10x the number of lines of code compared to other solutions, then pointing out such large discrepancies in code becomes a rather fair criticism on my part. I don’t mind 2, or 3 even solutions with 5 times. However when we reach 9x, then my point becomes rather fair.