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'd try declaring BlahBlah explicitly as a parameter and see if that makes a difference.

 

[RichardMGreen]

This is the updated SQL:-

PARAMETERS BlahBlah Text ( 255 );
SELECT DISTINCT qry_Nottingham_C4C_IDs.C4C_ID, qry_Nottingham_C4C_IDs.PCT, Member_Case_Lookup.Case_State, Member_Data.IPG5
FROM (qry_Nottingham_C4C_IDs INNER JOIN Member_Case_Lookup ON qry_Nottingham_C4C_IDs.C4C_ID = Member_Case_Lookup.MEMBER_C4C_ID) INNER JOIN Member_Data ON qry_Nottingham_C4C_IDs.C4C_ID = Member_Data.[C4C-ID]
WHERE (((qry_Nottingham_C4C_IDs.C4C_ID) Is Not Null) AND ((Member_Case_Lookup.Case_State)="open") AND ((Member_Data.IPG5)=[BlahBlah]))
ORDER BY qry_Nottingham_C4C_IDs.C4C_ID;

Unfortunately, I'm still having the same problem.

 

[RoryA]

What's the SQL for qry_Nottingham_C4C_IDs? (I assume that's a subquery)

 

[RichardMGreen]

It is a subquery, here's the SQL:-

SELECT MEMBUSR_NOPHI.C4C_ID, [IPG Structure].["L1"] AS PCT
FROM (MEMBUSR_NOPHI INNER JOIN ENNS ON MEMBUSR_NOPHI.MBUR_ID = ENNS.MEMB) INNER JOIN [IPG Structure] ON ENNS.INSCPRSRGRP = [IPG Structure].IPGP_ID
WHERE ((([IPG Structure].["L1"])="East Midlands") AND ((MEMBUSR_NOPHI.MRGEMSTR) Is Null));

 

[RoryA]

Nothing obvious there. How exactly are you calling this?

 

[RichardMGreen]

This is the code I'm using (just the relevant bits):-

Option Explicit
Public conn As ADODB.Connection, cmd As ADODB.Command, rs As ADODB.Recordset
Public twb As Workbook, wb As Workbook
Public querydata As Worksheet, ws As Worksheet, member_sheet As Worksheet, wsDst As Worksheet
Public rngData As Range, member_list As Range, rngDst As Range
Public db_pass As String, file As String, strConn As String, strQry As String, strSQL As String
Public savename As String, reportingdate As String, outputlocation As String, gp_code As String
Public counter As Integer, new_sheet_counter As Integer, vsion As Integer, lastrow As Integer
Public total_max_progress As Integer, total_progress As Integer, current_lastrow As Integer
Public member_max_progress As Integer, member_progress As Integer, total_members As Integer
Public member_count As Integer
Public response As VbMsgBoxResult, cancel_button As Boolean
Sub Import_data()
    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")
    Set conn = New ADODB.Connection
    Set cmd = New ADODB.Command
    file = querydata.Range("F2")
'----- Check database is in stated location -----
    If Dir(file) = "" Then GoTo db_location_error
'----- Get database password or cancel if required -----
    UserForm2.Show
    Unload UserForm2
    If cancel_button Then Exit Sub
'----- Set up connection to database and open it -----
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & file & _
        ";Jet OLEDB:Database Password=" & db_pass & ";"
    conn.ConnectionString = strConn
    On Error GoTo db_pass_error
    Application.ScreenUpdating = False
    conn.Open
    On Error GoTo 0
'----- Show userform and get list of members -----
    UserForm1.Show
    Call get_member_list
'----- Get member data and produce reports -----
    While member_list <> ""
        Call get_member_data
        Set member_list = member_list.Offset(1)
    Wend
'----- Destroy database connection and remove userforms -----
    Set cmd = Nothing
    Set conn = Nothing
    Unload UserForm1
    Application.ScreenUpdating = True
    For Each ws In Worksheets
        With ws.PageSetup
            .LeftFooter = ""
            .CenterFooter = ""
        End With
    Next
    Exit Sub
db_pass_error:
    response = MsgBox("Incorrect password for this database" & vbCrLf & _
        "Please contact your administrator", vbOKOnly, "XXX INCORRECT PASSWORD XXX")
    Application.ScreenUpdating = True
    Exit Sub
db_location_error:
    response = MsgBox("Database not in stated location" & vbCrLf & _
        "Please check the path and filename", vbOKOnly, "XXX DATABASE NOT FOUND XXX")
    Application.ScreenUpdating = True
    Exit Sub
End Sub
Sub get_member_list()
'----- Turn off screen flicker -----
    Application.ScreenUpdating = False
'----- Clear out old data and set up userform -----
    member_sheet.Rows("3:10000").ClearContents
    querydata.Select
'----- Get member list -----
    UserForm1.Label1.Caption = "Retrieving member list..."
    On Error GoTo 0
    While rngData.Value <> ""
        UserForm1.Label1.Caption = "Refreshing " & rngData.Value
        UserForm1.Repaint
        strQry = "[" & rngData.Value & "]"
        strSQL = "SELECT * FROM " & strQry
        cmd.CommandType = adCmdText
        cmd.CommandText = strSQL
        cmd.ActiveConnection = conn
        cmd.Parameters.Refresh
        If cmd.Parameters.Count > 0 Then
            cmd.Parameters(0) = querydata.Range("F5")
        End If
'----- Pick up information on where data is to go -----
        Set wsDst = Worksheets(rngData.Offset(, 1).Value)
        Set rngDst = wsDst.Range(rngData.Offset(, 2).Value)
'----- Retrieve data from database and insert into correct cells -----
        Set rs = cmd.Execute
        rngDst.CopyFromRecordset rs
'----- Make sure no data left to write and set up details for next query -----
        Set rs = Nothing
        Set rngData = rngData.Offset(1)
        UserForm1.Repaint
    Wend
    UserForm1.Label1.Caption = "Updating page footers and layout"
    UserForm1.Repaint
    For Each ws In Worksheets
        If InStr(ws.Name, "Raw_Data") = 0 Then
            With ws.PageSetup
                .LeftFooter = "Data Extraction Date " & Sheets("Query_List").Range("F18").Value
                .FitToPagesWide = 1
                .FitToPagesTall = False
                If ws.Name <> "Front_Page" Then
                    .PrintTitleRows = "$7:$7"
                End If
            End With
        End If
    Next
    Application.Calculate
    total_members = member_sheet.Range("F1")
    total_max_progress = member_sheet.Range("A65535").End(xlUp).Row + 1
    member_max_progress = querydata.Range("A65535").End(xlUp).Row - 4
    total_progress = total_progress + 1
    UserForm1.ProgressBar1.Value = (total_progress / total_max_progress) * 100
    UserForm1.Repaint
End Sub

There is another Sub which pulls back information on individuals but is basically the same code as get_member_list, just a different set of queries.
If any of this needs explaining, let me know.

 

[RoryA]

Does it make any difference if you replace this bit:

strQry = "[" & rngData.Value & "]"
        strSQL = "SELECT * FROM " & strQry
        cmd.CommandType = adCmdText
        cmd.CommandText = strSQL

with this:

strQry = "[" & rngData.Value & "]"
        cmd.CommandType = adCmdTable
        cmd.CommandText = strQry

 

[RichardMGreen]

I'm still getting the error message about the missing parameter. The parameter count is still zero.

 

[RoryA]

I'm afraid that without the database, I'm stumped. I can't see anything obvious about that query that would cause an issue.

 

[RichardMGreen]

I wish I could send it to you, but it's confidential info.