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]

Norie,
There's nothing in the Parameters collection (but there should be) - that's the whole problem.

 

[RichardMGreen]

Thanks for all the help so far, if you were fairly local there'd be aplenty for you.

This is the code I'm using for checking the parameter counts:-

Sub test_data()
    db_pass = ""
    Set twb = ThisWorkbook
    Application.ScreenUpdating = True
'----- Start setting up objects, set up database name/location -----
    Set querydata = Worksheets("Query_List1")
    querydata.Columns("D:D").ClearContents
    Set rngData = querydata.Range("A2")
    Set member_sheet = Worksheets("Member_sheet")
    Set member_list = member_sheet.Range("A3")
    file = querydata.Range("F2")
    Set db = Workspaces(0).OpenDatabase(file, False, False, "pwd=password;")
    Application.ScreenUpdating = False
    On Error GoTo 0
    While rngData.Value <> ""
        Set query = db.QueryDefs(rngData.Value)
        paramcount = query.Parameters.Count
        rngData.Offset(0, 4) = paramcount
        Set rngData = rngData.Offset(1)
        UserForm1.Repaint
    Wend
    Set query = Nothing
    db.Close
    Set db = Nothing
End Sub

It's telling me that the ODBC call failed on the file name.
I'm doing something wrong the the Set db line.

Any ideas?

 

[RoryA]

Do you have a password on the database? If so, is it a db password, or workgroup security or what?

 

[RichardMGreen]

It's a database password (password to open). The database is also encrypted if it makes any difference.

::edit::
Solved it. I needed a semi-colon just before pwd.
I'm also getting the right number of parameters for each query now.

 

[RoryA]

Your connect part needs to look like this then:

Connect:="MS Access;pwd=password"

 

[RichardMGreen]

Your connect part needs to look like this then:

Connect:="MS Access;pwd=password"

I've used the pwd bit but didn't seem to need the Connect section.

I'm just about to do a trial run and see what happens.

 

[RoryA]

Fingers crossed...

 

[RichardMGreen]

IT WORKS!!!!!

I feel I must ray: before your knowledge.

Have a theoretical and my thanks for all your help and patience.

 

[RoryA]

Glad to help.
Theoretical beer is about all I can cope with at the moment...

 

[RichardMGreen]

Can I be cheeky and ask just one more question?
Can I make a parameter in a query optional?

I'm talking about the usable C4CIDs query where it now accepts one parameter.
If I wanted the entire list, is it possible to bypass the parameter bit or pass it something so it collects everything?