OLEDB & Recordset

Hi Guys,
I'm busy trawling through documentation trying to puzzle out an issue I'm
having. Am about to tackle the SDK.
OK, I'm using MDAC 2.8. Visual Basic 6 and SQL2000 SP3a.
If I execute the following code through a normal ODBC connection (In Admin
tools), I get a result. Works fine.
If I execute the same code, using a UDL file using the "OLE DB
Provider for SQL Server", I get the following error when trying to grab the
value "Item cannot be found in the collection corresponding to the requested
name or ordinal". Also, if i assign a watch to the recordset using the OLEDB
provider,
a lot of the values are unavailable as the object is "Closed"
Dim rs1 As New ADODB.Recordset
Dim BarCode_Database As New ADODB.Connection
Dim sSql As String
sSql = "EXEC SP_Next_Position"
On Error GoTo Error
BarCode_Database.Open Connection_String
Set rs1 = BarCode_Database.Execute(sSql)
Get_Next_Barcode_Position = CStr(rs1!Current_Position)

I've tried messing around with Cursor possition and the like. Maybe I just
havn't hit the right combination :P .
Can anyone steer me in the right direction please?
MUCH appreciated,
Glynn
And what is Connection_string? From the code snippet and
from what you describe, it doesn't look like you are
actually opening a connection.
You'd open the connection using a UDL file with something
like the following on one line:
BarCode_Database.Open
"File Name=c:\PathTo\YourFile.udl"
Refer to the following for more information:
How To Use Data Link Files with ADO
http://support.microsoft.com/?id=189680
-Sue
On Fri, 28 Jan 2005 17:40:27 +0200, "Glynn"
<wwgze@.woolworths.co.za> wrote:

>Hi Guys,
>I'm busy trawling through documentation trying to puzzle out an issue I'm
>having. Am about to tackle the SDK.
>OK, I'm using MDAC 2.8. Visual Basic 6 and SQL2000 SP3a.
>If I execute the following code through a normal ODBC connection (In Admin
>tools), I get a result. Works fine.
>If I execute the same code, using a UDL file using the "OLE DB
>Provider for SQL Server", I get the following error when trying to grab the
>value "Item cannot be found in the collection corresponding to the requested
>name or ordinal". Also, if i assign a watch to the recordset using the OLEDB
>provider,
>a lot of the values are unavailable as the object is "Closed"
>--
>Dim rs1 As New ADODB.Recordset
>Dim BarCode_Database As New ADODB.Connection
>Dim sSql As String
>sSql = "EXEC SP_Next_Position"
>On Error GoTo Error
>BarCode_Database.Open Connection_String
>Set rs1 = BarCode_Database.Execute(sSql)
>
>Get_Next_Barcode_Position = CStr(rs1!Current_Position)
>
>--
>I've tried messing around with Cursor possition and the like. Maybe I just
>havn't hit the right combination :P .
>Can anyone steer me in the right direction please?
>MUCH appreciated,
>Glynn
>