Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1

    Unanswered: Run Access Saved Parameter Query From Excel ADO

    Hi all,

    Using Access/Excel 2007.

    I have a parameter query in in Access database.
    I would like to call that query, pass the value of the parameter, and return the results to Excel.

    My code is below.
    The error that is raising:
    Run-time error '3265':
    Item cannot be found in the collection corresponding to the requested name or ordinal
    Debug points here
    Code:
    .Parameters.Item("Text").Value = sParameter
    I also tried:
    Code:
    .Parameters.Item("[Text]").Value = sParameter
    With the same result

    How can I pass the parameter to the query?

    thx
    w

    Code:
    Option Explicit
    Sub AccessStoredQryADO()
        
        
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim sDBPath As String
        Dim sDB As String
        Dim sQry As String
        Dim sParameter As String
        Dim i As Integer
        
        Set wb = ThisWorkbook
        Set ws = wb.Worksheets("Qrys")
        
        With ws
        
            sDBPath = .Range("B5").Value
            sDB = .Range("B6").Value
            sQry = .Range("B7").Value
            sParameter = .Range("B8").Value
            
            'Test the database path name
             If Right$(sDBPath, 1) <> "\" Then sDBPath = sDBPath & _
             "\"
             
        End With
        
        Dim cn As ADODB.Connection
        Dim cm As ADODB.Command
        Dim rs As ADODB.Recordset
        
        'Connection String
            Set cn = New ADODB.Connection
            With cn
            End With
            cn.ConnectionString = _
            "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & sDBPath & sDB & ";" & _
            "Persist Security Info=False"
            cn.Open
            
        'ADO Command
         Set cm = New ADODB.Command
         
         With cm
            .ActiveConnection = cn
            .CommandType = adCmdStoredProc
            .CommandText = "qCustomerID"
            .Parameters.Item("[Text]").Value = sParameter
         
            'Recordset
             Set rs = .Execute() 'Set the recordset to the results of executing the access query
             
         End With
         
         With ws
            
            'Clear any previous data
             ws.Range("A13:XFD1048576").ClearContents ' Col XFD Excel 2007/2010 only
             
            'Transfer the recorset to the worksheet
             .Range("B13").CopyFromRecordset rs
             
            'Add headers
             For i = 1 To rs.Fields.Count
                .Cells(12, i).Value = rs.Fields(i - 1).Name
             Next i
                 
         End With
        
        'Tidy up
         'Destroy objects
          Set rs = Nothing
          Set cm = Nothing
          Set cn = Nothing
          Set ws = Nothing
          Set wb = Nothing
    
    
    End Sub

  2. #2
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    I'm getting closer by adding a parameter object
    But for some reason the code is returning the recordcount as -1. It should be 1
    The field names are output to the worksheet correctly but no records

    What might I be doing wrong here?

    thx
    w

    Code:
    Sub AccessStoredQryADOParamObj()
        
        
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim sDBPath As String
        Dim sDB As String
        Dim sQry As String
        Dim sParameter As String
        Dim i As Integer
        Dim RecCount As Long
        
        Set wb = ThisWorkbook
        Set ws = wb.Worksheets("Qrys")
        
        With ws
        
            sDBPath = .Range("B5").Value
            sDB = .Range("B6").Value
            sQry = .Range("B7").Value
            sParameter = .Range("B8").Value
            
            'Test the database path name
             If Right$(sDBPath, 1) <> "\" Then sDBPath = sDBPath & _
             "\"
             
        End With
        
        Dim cn As ADODB.Connection
        Dim cm As ADODB.Command
        Dim rs As ADODB.Recordset
        Dim pr As ADODB.Parameter
        
        'Connection String
            Set cn = New ADODB.Connection
            cn.ConnectionString = _
            "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & sDBPath & sDB & ";" & _
            "Persist Security Info=False"
            cn.Open
            
        'ADO Command
         Set cm = New ADODB.Command
         
         With cm
            .ActiveConnection = cn
            .CommandType = adCmdStoredProc
            .CommandText = "qCustomerID"
         
            'ADO Parameter
             Set pr = .CreateParameter("Text", adVarChar, adParamInput, 255)
             .Parameters.Append pr
             pr.Value = sParameter
         End With
         
        'Recordset
         Set cm.ActiveConnection = cn
         Set rs = cm.Execute 'Set the recordset to the results of executing the access query
    
         
         With ws
            
            'Clear any previous data
             ws.Range("A12:XFD1048576").ClearContents ' Col XFD Excel 2007/2010 only
             
             
            'Transfer the recorset to the worksheet
             .Range("B13").CopyFromRecordset rs
    
             
            'Add headers
             For i = 2 To rs.Fields.Count
                .Cells(12, i).Value = rs.Fields(i - 1).Name
             Next i
                 
         End With
        
        'Tidy up
         'Close recordsets
          rs.Close
        
         'Destroy objects
          Set pr = Nothing
          Set rs = Nothing
          Set cm = Nothing
          Set cn = Nothing
          Set ws = Nothing
          Set wb = Nothing
    
    
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •