Solved

Error 80040e21... rs.Open "SELECT ...

Posted on 2004-10-27
1,601 Views
Last Modified: 2008-01-09
Dim cn As ADODB.Connection
dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
    cn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DriverId=790;ReadOnly=True;" & "DBQ=" & "c:\book1.xls" & ";"
Set rs = New ADODB.Recordset

'****   Following line errors:  ODBC driver does not support the requested properties*****

    rs.Open "SELECT * FROM [SHEET1]", cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    While Not rs.EOF
        MsgBox rs(0)
    rs.MoveNext
    Wend
    rs.Close
Set rs = Nothing
    cn.Close
Set cn = Nothing

Why am I getting this error?
0
Question by:Mike Eghtebas
    6 Comments
     
    LVL 33

    Author Comment

    by:Mike Eghtebas
    Well, $ was missing from worksheet name.  I didn't think it is mandatory to have it there.

    Mike
    0
     
    LVL 14

    Accepted Solution

    by:
    You can find more info here on select statements for Excel sheets like selecting within CELL range's etc.

    http://support.microsoft.com/kb/257819/EN-US/
    0
     
    LVL 3

    Assisted Solution

    by:Egore
    You may want to look at this newsgroup thread (particularly the last two posts):

    http://groups.google.com/groups?hl=en&lr=&safe=off&th=7f28c50074fcaeb2&seekm=3d65a4c9%241%40dnews.tpgi.com.au&frame=off

    I'm not certain if that URL is permanent, but hopefully it is...
    0
     
    LVL 48

    Expert Comment

    by:Mikal613
    rs.Open "SELECT * FROM [SHEET1]", cn, 3, 2

    TRy this instead
    0
     
    LVL 48

    Assisted Solution

    by:Mikal613
    rs.Open "SELECT * FROM [SHEET1$]", cn, 3, 2

    I mean this sorry!
    0
     
    LVL 33

    Author Comment

    by:Mike Eghtebas
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

    875 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now