querying a spreadsheet with ADO, error value is not given for a required parameter

bfreescott

Board Regular
Joined
Aug 6, 2015
Messages
115
I am querying a closed workbook using an ADO connection string, which includes HDR=Yes in the extended properties.
(The connection is successful.)

My query is below:
rs.Open "SELECT c FROM [sheet1$B3:L29] WHERE Award = p", Conn, adOpenDynamic

Row 3 is where my column headers reside. (Award is one of the column headers.) c and p are string variables passed to the function that runs the ADO connection.

I am getting an error on the rs.open line that no value is given for a required parameter. I assume the query is not finding a value it is expecting and my educated guess is that is has something to do with the header, but I'm not certain how to fix my query. I've also tried setting HDR to No, but got the same error.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
You have hard coded c and p. Use the concatenation operator & to build your sql string. You may need embedded quotes around the value of p as well. Less that part...
Code:
rs.Open "SELECT " & c & " FROM [sheet1$B3:L29] WHERE Award = " & p, Conn, adOpenDynamic
I like to assign and sql to a string variable and then use Debug.Print to see what it resolves to in the Immediate window after a run.
 
Upvote 0
Thanks. I added the concatenation operators.
New rs.open line:
rs.Open "SELECT " & c & " FROM [sheet1$B3:L29] WHERE Award = " & p & ", Conn, adOpenDynamic"

I am getting a new error now:

The connection cannot be used to perform this operation. It is either closed or invalid in this context.
 
Upvote 0
You added too many quotes which quoted values that have to be resolved like Conn, your connection string, and adOpenDynamic which is a numerical constant.

Try embedding single quotes around your string value. e.g.
Code:
dim s as string
s = "SELECT " & c & " FROM [sheet1$B3:L29] WHERE Award = '" & p & "'"
debug.print s
rs.Open s, Conn, adOpenDynamic
There are several ways to do it.

In this next one, notice the use of fields/columns being f1, f2, etc.
Code:
'dups on another sheet
'northwolves, http://www.mrexcel.com/forum/showthread.php?t=371840
Sub Test()
  Dim sql As String
  With CreateObject("ADODB.connection")
  .Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;imex=1;hdr=no;';Data Source=" & ThisWorkbook.FullName
  sql = "SELECT f1,f3,f5,f8 FROM [sheet1$a2:h65536] where f1 in (select f1 from [sheet2$a2:a65536])"
  Sheet3.[a1].CopyFromRecordset .Execute(sql)
  .Close
  End With
End Sub
Code:
Sub t()
  GetData "x:\ado\ado.xls", "Sheet1", "A1:A1", Range("A1"), False, False
End Sub

'http://www.rondebruin.nl/ado.htm
'Copy a range from all files In a folder
Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
                   SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean)
' 30-Dec-2007, working in Excel 2000-2007
    Dim rsCon As Object
    Dim rsData As Object
    Dim szConnect As String
    Dim szSQL As String
    Dim lCount As Long

    ' Create the connection string.
    If Header = False Then
        If Val(Application.Version) < 12 Then
            szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 8.0;HDR=No"";"
        Else
            szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 12.0;HDR=No"";"
        End If
    Else
        If Val(Application.Version) < 12 Then
            szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 8.0;HDR=Yes"";"
        Else
            szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 12.0;HDR=Yes"";"
        End If
    End If

    If SourceSheet = "" Then
        ' workbook level name
        szSQL = "SELECT * FROM " & SourceRange$ & ";"
    Else
        ' worksheet level name or range
        szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];"
    End If

    On Error GoTo SomethingWrong

    Set rsCon = CreateObject("ADODB.Connection")
    Set rsData = CreateObject("ADODB.Recordset")

    rsCon.Open szConnect
    rsData.Open szSQL, rsCon, 0, 1, 1

    ' Check to make sure we received data and copy the data
    If Not rsData.EOF Then

        If Header = False Then
            TargetRange.Cells(1, 1).CopyFromRecordset rsData
        Else
            'Add the header cell in each column if the last argument is True
            If UseHeaderRow Then
                For lCount = 0 To rsData.Fields.Count - 1
                    TargetRange.Cells(1, 1 + lCount).Value = _
                    rsData.Fields(lCount).Name
                Next lCount
                TargetRange.Cells(2, 1).CopyFromRecordset rsData
            Else
                TargetRange.Cells(1, 1).CopyFromRecordset rsData
            End If
        End If

    Else
        MsgBox "No records returned from : " & SourceFile, vbCritical
    End If

    ' Clean up our Recordset object.
    rsData.Close
    Set rsData = Nothing
    rsCon.Close
    Set rsCon = Nothing
    Exit Sub

SomethingWrong:
    MsgBox "The file name, Sheet name or Range is invalid of : " & SourceFile, _
           vbExclamation, "Error"
    On Error GoTo 0

End Sub
 
Last edited:
Upvote 0
Yep, I saw that as I was researching the new error. (You have to double-check on these query lines because the VBE will auto-add a double-quote on you at the end of the line.)

New line is:
rs.Open "SELECT '" & c & "' FROM [sheet1$B3:L29] WHERE Award = '" & p & "'", Conn, adOpenDynamic

The query is now working, so thanks for your help on the quotes. I'm off to determine now why it isn't returning the correct values! :LOL:
 
Upvote 0
Maybe:
Code:
rs.Open "SELECT '" & c & "' FROM [sheet1$B3:L29] WHERE f " & wb.worksheets("Award").Column & " = '" & p & "'", Conn, adOpenDynamic
where wb is ThisWorkbook, ActiveWorkbook, or other, or just hardcode the generic fieldname.
 
Upvote 0
Thanks Kenneth

I just changed my select to * and then as I read through the records, I return c.

Code:
If Not rs.EOF And Not rs.BOF Then
            rs.MoveFirst
            Do
                Get_WB_Numbers = rs(c)
                rs.MoveNext
            Loop Until rs.EOF
        End If

Sometimes a field I am searching for is not in the table however and I get the dreaded 3265 Item cannot be found error. Can you see any reason why I couldn't or should use Resume Next in those cases?
 
Upvote 0
Depends on your needs but offhand, I would say that it should be ok.

You could read the fieldnames into an array and check for a specific fieldname if you wanted to avoid that sort of error.
 
Upvote 0
I've always been a little array-phobic because most of the examples I've seen were cases where values are read into the array and then pasted in one big block to the sheet. But in this case, that's essentially what I want to do. How would I need to edit my Do Loop to read the c values into an array?
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top