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]

I've just put all the linked tables back to linked tables and I'm back to square one again.
Must be something to do with the linked tables???

 

[RichardMGreen]

I've narrowed it down to at least one of the linked tables.
When I put CASE back to a linked table from a local one, qry_Nottingham_Usable_C4C_IDs loses the fact that needs a parameter (the parameter count goes to 0).

I'm still checking to see what others are causing me problems.

 

[RoryA]

What type of DB is the link source? Still Access?
CASE is not a great name for a table by the way, as it's a SQL keyword.

 

[RichardMGreen]

All the linked tables point to CSV files. I don't think it would cause me any problems to rename it if necessary.
Another oddity is that I've just made the linked table into a local copy and kept the data and now qry_Nottingham_Usable_C4C_IDs says it wants 2 parameters.

::edit::
I've solved the 2 parameter problem, I'd left the explicit parameter naming in place.

 

[RoryA]

It should not be a problem to have explicit parameters, unless the names did not match up.

 

[RichardMGreen]

The names didn't match so I deleted the explicit parameter. I'll put it back in and try again.

::edit::
I've put the explicit parameter back in and changed the table name to CASE1. The parameter count is still showing as zero.

 

[RoryA]

Very odd. I switched to using DAO with a linked table version of the CASE table, and it works fine. Perhaps that's the way to go?

 

[RichardMGreen]

At the moment, I'll try anything.
How much difference is there between using DAO and the way I'm doing it now?

 

[RoryA]

As a rough idea:

   Dim db As DAO.Database
   Dim Qd As DAO.QueryDef
   Dim rs As DAO.Recordset
      db_pass = ""
    Set twb = ThisWorkbook
    Application.ScreenUpdating = True
'----- Start setting up objects, set up database name/location -----
    total_members = 0
    member_count = 0
    total_max_progress = 0
    total_progress = 0
    member_max_progress = 0
    member_progress = 0
    Set querydata = Worksheets("Query_List")
    Set rngData = querydata.Range("A2")
    Set member_sheet = Worksheets("Member_sheet")
    Set member_list = member_sheet.Range("A3")
    file = querydata.Range("F2")
   ' DAO bit
   Set db = Workspaces(0).OpenDatabase(file, ReadOnly:=True, _
   Options:=False)
   ' get query
   Set Qd = db.QueryDefs(rngData.Value)
   If Qd.Parameters.Count > 0 Then
      Qd.Parameters(0).Value = "test"
   End With
   Set rs = Qd.OpenRecordset

Oddly, your qry_Nottigham_C4C_IDs does return a correct parameter count (if you add a parameter) when called from ADO, but not when it's called as a subquery of the Usable_ version.

 

[Norie]

Richard

This has probably been suggested but have you tried finding out more about the parameter(s) by looping through the Parameters collection?

Apologies if that has been suggested, it's just something I've done in the past when having problems with parameters.

Usually turns out to be a type mismatch on my part.