ADO SQL Problem

[Todd Huttenstine]

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 spreadsheet
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"
' worksheet
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 worksheet 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


Thanks
Todd

 

[MGFoster]

-----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-----

 

[Jamie Collins]

Probably there aren't any columns after oRS(0).

Another example of why SELECT * should be avoided. Always specify the
columns you require e.g.

SELECT MyCol1, MyCol2, MyCol3 FROM Range1;

Same applies to your INSERT INTO syntax e.g.

INSERT INTO Range1
(MyCol1, MyCol2, MyCol3)
VALUES (1,2,3) ;

Jamie.

--