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

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

user1483
1#
user1483 Published in June 21, 2018, 8:41 am

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.

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO