none
Problems with an ADODB record set RRS feed

  • Question

  • Hi Iam just getting back into the swing of things after many years off. I am trying to read a simple table in MSACCESS using the ADODB. My problem is with accessing the columns in the recordset by column name. I have looked through several of the blogs and post but have not found the answer as to why or what I am doing wrong any help would be greatly appreciated.

    Imports ADODB
    Imports System
    Imports Microsoft.VisualBasic
    Public Class Form1
        Sub DBConnection()
            On Error GoTo HandleErrors
            ' Do something in here that
            ' might raise an error.
            ' Get the connection string         
            Dim dbProvider As String
            Dim dbDriver As String
            Dim dbSource As String
            Dim dbname As String
            Dim dbaccess As String
            Dim conString As String
            Dim cnn As ADODB.Connection
            Dim rs As ADODB.Recordset
            cnn = New ADODB.Connection
            dbProvider = "Microsoft.ACE.OLEDB.12.0"
            dbSource = "C:\TOPS400\XX\"
            dbname = "TOPS_RPT.mdb"
            dbaccess = "Jet OLEDB:Database Password="
            conString = "Provider=" & dbProvider & "; Data Source=" & dbSource & dbname & ";" & dbaccess & ";"
            ' Open the connection 
            cnn.Open(conString, , , 0)
            If cnn.State = CInt(ADODB.ObjectStateEnum.adStateOpen) Then
                TextBox1.Text = "Welcome to Pubs!"
            Else
                TextBox1.Text = "Sorry. No Pubs today."
            End If
            ' Create a Recordset by executing an SQL statement.
            rs = cnn.Execute("Select [Owner ID], [Owner1 Full Name] From [Owners File];")
            rs.MoveFirst()
            While Not rs.EOF
                TextBox1.Text = rs.Fields("[Owner ID]").Value
                '            TextBox1.Text = rs.Fields(2).Value
                rs.MoveNext()
             End While
            ' Close Connection
    ExitHere:
            ' Perform cleanup code here.
            ' Disregard errors in this
            ' cleanup code.
            On Error Resume Next
            ' Perform cleanup code.
            cnn.Close()
            Exit Sub

    Thursday, November 15, 2012 6:35 PM

Answers

  • Try rs![Owner ID] or rs("Owner ID")

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    • Marked as answer by davidb1255 Thursday, November 15, 2012 8:58 PM
    Thursday, November 15, 2012 8:26 PM
  • When using the Fields collection, don't enclose the field names in square brackets [ ], even if the field names contain spaces:

               TextBox1.Text = rs.Fields("Owner ID").Value

    Alternatively, use the ! notation; this does require square brackets if the field name contains spaces:

               TextBox1.Text = rs![Owner ID].Value


    Regards, Hans Vogelaar

    • Marked as answer by davidb1255 Thursday, November 15, 2012 8:58 PM
    Thursday, November 15, 2012 8:32 PM

All replies