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?

 

[RoryA]

I wouldn't actually need any data in it, just the structure (i.e. empty tables) and queries, but I completely understand.

 

[RichardMGreen]

If you don't need the data in it, I think I can send an empty version. The table names probably wouldn't make any sense and the linked tables would be empty but all the queries should be OK.

I'll have to check first though.

 

[RichardMGreen]

Rorya - I've had the OK to send you the empty database. What would happen to the linked tables? Would they just come up as empty or give an error? Where do you want it sending to?

 

[RoryA]

Linked tables might be an issue - can you create local copies? I will PM you an email address.

 

[RichardMGreen]

I've just sent you a copy of the database, it's still 2.2MB even when empty!!!

 

[RoryA]

Not sure if it's good news or bad news, but both of those queries work for me. Parameters.Count is 1 in each case.

 

[RichardMGreen]

Any idea why I would be getting zero then?

 

[RoryA]

Not sure. Did you test on the copy you sent me to see if the error occurs there?

 

[RichardMGreen]

No I didn't, I just assumed it would give me the same error. I'll test it tomorrow when I'm back at work.

 

[RichardMGreen]

I've just tested some code on the same database I sent to you and I get a parameter count of 1 for all the queries except one (which shouldn't have one anyway).

Now I'm properly stumped.