macro returns error "Method 'Open' of object '_Recordset' failed"

Re: http://www.excelforum.com/excel-prog...ml#post2138532, this is *exactly* the issue that I have. I think Djvice was working with the same UPC db file that I dl from the web, too. I loaded the files in the zip on that thread and got the lookup macro to work fine.

But then I tried with my data file. The dl data file "items.csv" has 1,048,571 rows of data. I renamed djvice.xls to mylookup.xls. I edited the macro to this

Sub aaa()
  Range("B:C").ClearContents
  Set cn = CreateObject("adodb.connection")
  Set rs = CreateObject("adodb.recordset")
  
  cn.Open "provider=microsoft.jet.oledb.4.0;data source = C:\Documents and Settings\Lenovo_User\My Documents\My Data Sources\upcdirectory;extended properties = ""text; hdr=yes"""
  For Each ce In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
  
    rs.Open "select size,description from items.csv where UPC = " & ce.Value, cn, 3, 3
    Cells(ce.Row, 2).CopyFromRecordset rs
    rs.Close
  Next ce
  
  
  
  Set rs = Nothing
  Set cn = Nothing
  
End Sub

where items.csv is my data file. For the header, in the first three cells of the first row of this data file I have inserted "UPC", "size" and "description". When I run the macro I get this error message from MS VB:

Run-time error '-2147467259 (80004005)':
Method 'Open' of object '_Recordset' failed

Help, please. TIA

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Hi

I could get it to work by enclosing the field names in square brackets:

Sub aaa()
  Range("B:C").ClearContents
  Set cn = CreateObject("adodb.connection")
  Set rs = CreateObject("adodb.recordset")
  
  cn.Open "provider=microsoft.jet.oledb.4.0;data source = C:\Documents and Settings\Lenovo_User\My Documents\My Data Sources\upcdirectory;extended properties = ""text; hdr=yes"""
  For Each ce In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
  
    rs.Open "select [size],[description] from items.csv where [UPC] = " & ce.Value, cn, 3, 3
    Cells(ce.Row, 2).CopyFromRecordset rs
    rs.Close
  Next ce
  
  
  
  Set rs = Nothing
  Set cn = Nothing
  
End Sub

Richard