CODEWITHSUNDEEP
sql-servervbams-accessstored-proceduresparameter-passing
Michael
Michael

Reputation: 2657

Access Call Stored Procedure with Pass Through Parameter

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

Upvotes: 3

Views: 13214

Answers (2)

Albert D. Kallal
Albert D. Kallal

Reputation: 48999

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.

Upvotes: 3

Gord Thompson
Gord Thompson

Reputation: 123419

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)

Upvotes: 4

Related Questions