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?

 

[dk]

Does the code work if you remove the parameter from the query in Access and comment out the parameter parts of your code in Excel? Your code worked fine for me using a test database and a very simple table so it might be something going on with the actual query (as I think you mentioned before). Please try and run the non-parameterised query through your ADO code and let us know if that works.

DK

 

[RichardMGreen]

It does.
Another oddity is that if I bypass the error in the VBA (by forcing a continue by moving the highlighted line), it works. I.e. the parameter updates and it brings back the correct information.

 

[RichardMGreen]

Anyone ..... please?

 

[dk]

Hi Richard

I did have a play around with your problem today and I must say that I'm stumped! If I step through the code I can see that the Parameters collection gets populated as soon as the database is connected and the command string is specified - I tried opening the connection first and then setting the command text and vice versa and in both cases the parameter appears (I was looking in the Locals window while doing this).

I was able to get the same error message if I just tried to set a non-existent parameter e.g.

cmd.Parameters(0) = param1 'Valid
cmd.Parameters(1) = 123 'Gives error 3265 item cannot be found in the collection...

I am curious as to whether there is a delay in connecting to your database - is it remote or connecting over a slow network (this is where you tell me it's sat in the same folder on your C drive).

This is a long shot but can you see what this does. Try these lines just before you execute the command:

      While cmd.Parameters.Count = 0
 	     DoEvents
      Wend

If there is some sort of delay in populating the parameters then this may work...

EDIT: I should add that if this doesn't work then you'll be stuck in an infinite loop which you can stop using Ctrl-Break (you probably know that but just in case )

HTH
DK

 

[RichardMGreen]

That might not be such a bad idea, the database is sat on a network drive.
Don't know if it makes any difference, but this query I'm having trouble with is at the end of a list of queries that all work OK.

Think I might have to chuck the PC out of the window and see if that helps!!!

 

[dk]

OK, what happens if you move the order of the queries (or do they have to happen in a specific order)?

 

[RichardMGreen]

I haven't tried the loop as yet (not had time unfortunately).
I've tried reordering the queries (the order doesn't really matter) but I still end up with the same error.

 

[xenou]

See my post #15 on a workaround (if you wish to try a different approach).

ξ

 

[RichardMGreen]

I'm trying to sort this out for the Access-challenged (to borrow a phrase) and some of the SQL queries are quite lengthy.

I don't think designing them in VBA would be practical unfortunately.

 

[xenou]

In your SQL:

FROM (Member_Data LEFT JOIN
Member_Address
ON Member_Data.[C4C-ID] = Member_Address.[C4C ID])
LEFT JOIN [Care Plan Report - Member Demographics]
ON Member_Data.[C4C-ID] = [Care Plan Report - Member Demographics].MEMBER_C4C_ID

Access likes to have queries with multiple joins fully parenthesized:
FROM ((Member_Data LEFT JOIN
Member_Address
ON Member_Data.[C4C-ID] = Member_Address.[C4C ID])
LEFT JOIN [Care Plan Report - Member Demographics]
ON Member_Data.[C4C-ID] = [Care Plan Report - Member Demographics].MEMBER_C4C_ID)

----------------------------------------------------------------------------------------
Also, Name is a reserved word and not a good one for use as a field identifier:
Member_Address![First Name] & " " & Member_Address![Last Name] AS Name

Nor is it clear why in the above line you use a ! operator and nowhere else. I have never used one in a query passed through ADO and it may/may not be a trouble spot.