Using Excel VBA to construct a multi parameter query in an Access database

Hi

I need to create an Excel VBA statement to query an Access Stored Procedure. I can get it to work with one parameter but I have a query where I need to pass two parameters. The query code in Access is as follows and it works when run directly on the stored procedure

SELECT Sales_Order_Location.Sales_Order_Num, Sales_Order_Location.Span_Number, Sales_Order_Location.Span_Start_Location, Sales_Order_Location.Span_Start_Demarc, Sales_Order_Location.Span_End_Location, Sales_Order_Location.Span_End_Demarc, Sales_Order_Location.Span_Fibre_length, Sales_Order_Location.IP_POP, Sales_Order_Location.Notes
FROM Sales_Order_Location
WHERE (((Sales_Order_Location.Sales_Order_Num)=[SO]) AND ((Sales_Order_Location.Span_Number)=[S]));


The Excel VBA I'm trying to use is as follows :

LocSearch = Me.txtLocNumber + Me.SpinButton1.Value ' gives a number

LineRef = Me.txtLocNumber ' gives a string
 
 
 On Error Resume Next
 MyRecordset.Close
 On Error GoTo 0


 
 Set MyCommand.ActiveConnection = MyDatabase
 MyCommand.CommandText = "Location_Span_Query"
 MyCommand.CommandType = adCmdStoredProc
 With MyCommand
 .Parameters.Refresh
  .Parameters.Append .CreateParameter("QueryLongParam", adBigInt, adParamInput, , LocSearch)
  .Parameters.Append .CreateParameter("QueryTextParam", adVarChar, adParamInput, 255, LineRef)
  End With
 
 MyRecordset.Open MyCommand, , adOpenDynamic, adLockPessimistic
 
 MsgBox "Records found " & MyRecordset.RecordCount

I'm not sure how to apply the appropriate parameter to the right field (or even if this is possible).

Can anyone assist?

Thanks

Pete




Start Free Trial