Excel passing parameters to Access for queries

[RichardMGreen]

Hi all

I've got a piece of code in Excel which links to an Access database and runs queries in it.
Some of the queries need parameters which are being passed to it using this piece of code:-

If param1 <> "" Then cmd.Parameters(0) = param1

The query I'm running requires 1 parameter which is contained in param1 (and I've stepped through the code to make sure it's there).

When I try to pass the parameter, I get the following error message and the code stops:-
Item cannot be found in the collection corresponding to the requested name or ordinal

If I miss out the the line of code above (leaving the parameter empty) and try to execute the query, I get the following error message:-
Too few parameters. Expected 1

It looks like I need to pass the parameter, but when I do it isn't accepted.

Anyone any ideas?

 

[RichardMGreen]

@Xenou

Just tried making the changes you suggested (including changing the ! for a .) and Name is now Full_Name ion the query.

Unfortunately, I'm still getting the same error.

 

[RichardMGreen]

That's it, I've just cheated.
I've put in an IF statement to stop passing parameters to the query that's causing me problems.

Everything seems to work OK, but I'd love to figure out why this one query won't allow me to pass a parameter to it when it needs one.

 

[xenou]

It's a mystery to me. But I've never used ADO parameters without explicitly declaring them on both sides (in the query, and using a defined parameter object in the code). I don't know if my strict habits are worth the effort or not ... but you are using variants and undefined parameters which (to me) leaves things up to the database/code to decide "for you". In other words, I'd normally pass a named parameter with a defined size, type, and value to a query that expects a named parameter of the same size and type. I may experiment with your setup however - it's interesting.

Is this, by the way, the only query that uses this Member_ID parameter? Is it a number or text data type? What is the actual value in the Excel cell that you pass through? Is it a number or text?

 

[RichardMGreen]

Al the queries in the database use the same parameter.
It's in the format 00-00000000 so I suppose it's classed as text because of the dash.

I've done this sort of parameter passing before, so I'm not sure why it's failed this time.

 

[xenou]

What about this line in your query:


FROM (Member_Data LEFT JOIN Member_Address
ON Member_Data.[C4C-ID] = Member_Address.[C4C ID])

Should that second field be C4C-ID and not C4C ID

 

[RichardMGreen]

C4C ID is a reference number that's used to identify the records I want to retrieve.
That's how it appears on the downloads.

 

[xenou]

Well - at this point I'm ready to give up. If I really needed to know and if I were you I would isolate the query in a test environment (removed from all the other variables that are at play in your full blown project) - break it down into parts to isolate out where it go goes wrong, either through deconstruction or reconstruction.

ξ

 

[RichardMGreen]

I've been battering at this for about a week now and still not figured it out.
There must be something odd in the query but I just can't see it.

Thanks to one and all for the assistance.

 

[dk]

Richard - did you ever try the loop I suggested in post 24?

 

[RichardMGreen]

I did and, as you thought might happen, I got caught in the infinite loop.
There's something peculiar about the query in that it needs a parameter but refuses to accept one ..... unless you try to not pass it one and then it needs one.

As per post #32 if put an IF statement around the query in question and that seems to work ...... for now.