Parameter queries from Access 97 to SQL Server 7 stored procs

I have created a stored proc on SQL Server 7 that searches customer records based on a post code entered:

REATE PROCEDURE SearchPostCode
@PostCode char(8)
AS
select address6, customer, name, address1, address2, address3, address4 from
scheme.slcustm
where
address6 like @PostCode + "%"
order by address6

This runs fine and I can call as I want from VB. If I call from Access 97, the value of the parameter must be embedded as a literal value in the SQL pass-through query, eg SearchPostCode WN6 returns all WN6 post codes. What I want to do is parameterise the Access query so that I can pass a user value into the query which then goes through to SQL Server. When I try to add the parameters statement to the top of my Access query,
PARAMETERS PostCode char(8);
SearchPostCode PostCode

I get the following:
ODBC Call Failed
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:Incorrect syntax near 'char'.(#170)

I've tried using char, char(), text but to no avail.

Also tried putting an @ before the parameter name but this gives me an error message to declare the variable.

What am I doing wrong, as you must be able to pass parameters into the Access query from the calling app (an Access report called from a VB in my case) that passes through to the stored procedure.

As an afterthought, can I pass the value from a control on a VB form to do this, such as a text box. Not tried this approach.

Many thanks in advance

Dave Seddon

The thing to do here is to alter then query as needed. You do this by altering the querydef.

dim qd qs querydef
set qd = currentdb.querydefs("SearchPostCode")
qd.sql = "SearchPostCode " & codetolookfor
qd.close
' refresh whatever needs to be refreshed




Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

Excellent reply. I was just going down the route of rebuilding the entire SQL complete with parameters, but much more logical to keep the sp_name and rreplace the params as required.

Thanks again