Refering to fields usind ADO Controls?

[Roselene]

Hi, everybody!

I have a Table which is the record source of an ADO Data Control. I'd like to refer to a field in this table using the ADO Control.
So, I've tried:
MsgBox Adodc1.Recordset.Fields.Item(2)
MsgBox Adodc1.Recordset(0)
MsgBox Adodc1.Recordset("TheFieldName")

But I always receive the following error message:
Run time error '91' - object variable or with block variable not set

so, I've taken a look into the help file and
changed my code like this:
Dim MyAdo As Object
Set MyAdo = Adodc1
MsgBox MyAdo.Recordset.Fields.Item(2)
MsgBox MyAdo.Recordset(0)

But the error message still appears!

Thanks in advance for any ideas!
Roselene

[RogerH]

Hi,

first of all: Have you opened the recordset already?

DIM MyADORecSet as Recordset

Set MyADORecset = New Recordset
MyADORecset.Open "SELECT * FROM Table"

You the access the values with the field-collection:

MsgBox MyADORecset.Fields("Fieldname").Value

Roger

[RogerH]

Hi Roselene,

I tried

Dim MyADORecSet As Recordset
Set MyADORecSet = New Recordset
MyADORecSet.Open "Select * from Mytable"
MsgBox MyADORecSet.Fields("TAG").Value

The 2. line works, 3. line reports error because no connection is provided.
You have to add a reference (Project - References...) on the Microsoft ActiveX Data Objects 2.x libray.

To the connection: I mostly use the connection string "File Name=" with an UDL-File. An UDL-file can be created by right click in Windows explorer, new, Microsoft Data link file. A double-click on that file opens a dialog, where u can define the data source. Advantage is, that by changing the UDL-file, you can change the data source e.g. from Access to SQL server without changing code.

I hope that helps.
Roger

[Roselene]

Roger,

Thanks for taking the time to help me. I really appreciate that.

I've tried
Dim MyADORecSet As Recordset
Set MyADORecSet = New Recordset
MyADORecSet.OpenRecordset "Select * from Mytable"
MsgBox MyAdoset.Fields("TAG").Value

But VB displays a error message regarding the 2nd line (Set MyADORecSet = New Recordset
):
Invalid use of New keyword.

Do you have any ideas on what I am doing wrong?

Thanks again,
Roselene