Parameter queries from Access 97 to SQL Server 7 stored procs

[dave seddon]

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

[Cakkie]

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
slisse@planetinternet.be

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

[dave seddon]

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