Home SQL Select query - Item can not be found in the collection to the requested name or ordinal
Reply: 1

SQL Select query - Item can not be found in the collection to the requested name or ordinal

Alfa Bravo
1#
Alfa Bravo Published in 2018-01-12 14:19:36Z

I have the following block of code that I got from a site

Sub RunSELECT()
    Dim cn As Object, rs As Object, output As String, sql As String

    '---Connecting to the Data Source---
    Set cn = CreateObject("ADODB.Connection")
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        .Open
    End With


    '---Run the SQL SELECT Query---
    sql = "SELECT * FROM [Shops$] WHERE ShopCode > 6000" 
    Set rs = cn.Execute(sql)

    Do
       output = output & rs(0) & ";" & rs(1) & ";" & rs(2) & vbNewLine
       Debug.Print rs(0); ";" & rs(1) & ";" & rs(2)
       rs.Movenext
    Loop Until rs.EOF
    MsgBox output

    '---Clean up---
    rs.Close
    cn.Close
    Set cn = Nothing
    Set rs = Nothing
End Sub

The code above runs fine, but when I change the select statement to:

sql = "SELECT [Shops$].[ShopCode] FROM [Shops$] WHERE ShopCode > 6000" 

I get the error

Item can not be found in the collection to the requested name or ordinal

I have tried many things for hours now including deleting my colm headers and trying different things, but I do not seem to be able to get it to recognize my colm headers.

Rory
2#
Rory Reply to 2018-01-12 14:29:24Z

If you are only selecting one field, you cannot then refer to 3 of them in the later code, so you need to amend these lines:

   output = output & rs(0) & ";" & rs(1) & ";" & rs(2) & vbNewLine
   Debug.Print rs(0); ";" & rs(1) & ";" & rs(2)

to perhaps this:

   output = output & rs(0) & vbNewLine
   Debug.Print rs(0)
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.310559 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO