Re: ADO SQL Problem

From: MGFoster (me_at_privacy.com)
Date: 07/20/04 

Date: Tue, 20 Jul 2004 03:19:43 GMT

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Probably there aren't any columns after oRS(0). Put a break at the line
"oRS(1).Value = 8". Run the routine. When it stops, in the Debug
window, execute:

? oRS.Fields.Count

According to your code, you are expecting 3 (or more) columns (fields).
If the command returns only "1" then that is the reason the error is
occurring on oRS(1).Value = 8: that object oRS(1) isn't there. 

-- 
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQPyPOoechKqOuFEgEQKf5QCeLC1pWiNxH1xdJmbMv2xMo/hutCIAoJZu
qTB4a+EIVFr9Q41Nx+fq74tj
=t4NL
-----END PGP SIGNATURE-----
Todd Huttenstine wrote:
> Below code gives me the error: Item cannot be found in the 
> collection corresponding to the requested name or ordinal.
> 
> Can anyone tell me why.  I get this error near the end of 
> the code on line "oRS(1).Value = 8"
> 
> 
> 'Using OLE DB Provider for Jet:
> 
> Dim oConn As ADODB.Connection
> Dim oCmd As ADODB.Command
> Dim oRS As ADODB.Recordset
> 
> ' Open a connection to the Excel spread***
> Set oConn = New ADODB.Connection
> oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>            "Data Source=E:\QTD\Sales.xls;" & _
>            "Extended Properties=""Excel 8.0;HDR=Yes;"";"
> 
> ' Create a command object and set its ActiveConnection
> Set oCmd = New ADODB.Command
> oCmd.ActiveConnection = oConn
> 
> ' This SQL statement selects a cell range in the "Expenses"
> ' work***
>     oCmd.CommandText = "SELECT * from `Expenses$A2:C4`"
> 
> ' This SQL statement selects a named cell range
> ' defined in the workbook
> oCmd.CommandText = "SELECT * from `Range1`"
> 
> ' Open a recordset containing the work*** data.
> Set oRS = New ADODB.Recordset
> oRS.Open oCmd, , adOpenKeyset, adLockOptimistic
> 
> Debug.Print oRS.RecordCount
> 
> ' Update last row
> oRS.MoveLast
> oRS(0).Value = "test"
> oRS.Update
> 
> ' Add a new row
> oRS.AddNew
> oRS(0).Value = 7
> oRS(1).Value = 8
> oRS(2).Value = 9
> oRS.Update
> Debug.Print oRS.RecordCount