none
Reference to DAO RRS feed

  • Question

  • I am trying to show Access records in a ListBox, using DAO (based on an example I saw in a book).  I did Project > Add Reference > COM > Microsoft DAO 3.6 Object Library.

     

    I am getting lots of squiggle lines; such as:

    CN.ConnectionString

    CN.Open

    CM.ActiveConnection

    CM.CommandType

    CM.CommandText

    CM.Execute

    RS.ActiveConnection

    RS.Open

     

    There are some others too.  I suspect this is because my reference isn’t right, but if it’s not supposed to be a reference to DAO, then I don’t know what to reference.

     

    My code is below:

     

    Public Class Form1

        Dim CN As New Connection

        Dim CM As New Command

        Dim RS As New Recordset

     

        'Public Class Form1

     

        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As  _

         System.EventArgs) Handles MyBase.Load

            Try

                CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

                        "'C:\Users\Excel\Desktop\Coding\Microsoft Access\Powerful Access Files\Nwind.mdb'"

                CN.Open()

                CM.ActiveConnection = CN

                CM.CommandType = CommandTypeEnum.adCmdText

                CM.CommandText = "SELECT * From Suppliers"

                CM.Execute()

                RS.ActiveConnection = CN

                RS.Open(CM, , CursorTypeEnum.adOpenDynamic _

                        , LockTypeEnum.adLockOptimistic)

                Do Until RS.EOF

                    Me.ListBox1.Items.Add(RS.Fields(1).Value)

                    RS.MoveNext()

                Loop

                Me.TextBox1.Enabled = False

            Catch ex As Exception

                MsgBox(ex.Message)

            End Try

        End Sub

     

        Private Sub ListBox1_Click(ByVal sender As Object, ByVal e As  _

                 System.EventArgs) Handles ListBox1.Click

            RS.MoveFirst()

            RS.Move(Me.ListBox1.SelectedIndex)

            Me.TextBox1.Text = CStr(RS.Fields(0).Value)

            Me.TextBox2.Text = CStr(RS.Fields(1).Value)

            Me.TextBox3.Text = CStr(RS.Fields(2).Value)

            Me.TextBox4.Text = CStr(RS.Fields(5).Value)

        End Sub

     

        Private Sub Button1_Click(ByVal sender As Object, ByVal e As  _

            System.EventArgs) Handles Button1.Click

            RS.Fields(1).Value = Me.TextBox2.Text

            RS.Fields(2).Value = Me.TextBox3.Text

            RS.Fields(5).Value = Me.TextBox4.Text

            RS.Update()

            MsgBox("Record Saved")

        End Sub

     

        Private Sub Button2_Click(ByVal sender As Object, ByVal e As  _

                System.EventArgs) Handles Button2.Click

            RS.AddNew()

            Button1_Click(sender, e)

            Me.ListBox1.Items.Clear() 'Not: Me.ListBox1.Clear()

            RS.MoveFirst()

            Do Until RS.EOF

                     Me.ListBox1.Items.Add (RS.Fields(1).Value

                RS.MoveNext()

            Loop

        End Sub

    End Class

     

     

    I have all the appropriate objects on the Form.

     

    Thanks everyone!!

    Saturday, November 26, 2011 9:14 PM

Answers

  • Hi Rex

    This is not really a VSTO question. I'm just not sure where it does belong as you don't mention the version of Access involved, although I'd tend to say a forum that deals with data connections or with Windows Forms data controls (probably the latter)...

    Since you use a connection string containing "OLEDB", DAO is certainly not the correct reference, in that case you'd need ADO (ActiveX data objects) or, more likely, ADO.NET (the System.Data namespace).

    And if this is a Windows Forms project, as it seems to be, then it is possible to have the project "bind" to the controls, rather than you're needing to fill them "manually".


    Cindy Meister, VSTO/Word MVP
    • Marked as answer by ryguy72 Sunday, November 27, 2011 6:06 PM
    Sunday, November 27, 2011 7:14 AM
    Moderator

All replies

  • Hi Rex

    This is not really a VSTO question. I'm just not sure where it does belong as you don't mention the version of Access involved, although I'd tend to say a forum that deals with data connections or with Windows Forms data controls (probably the latter)...

    Since you use a connection string containing "OLEDB", DAO is certainly not the correct reference, in that case you'd need ADO (ActiveX data objects) or, more likely, ADO.NET (the System.Data namespace).

    And if this is a Windows Forms project, as it seems to be, then it is possible to have the project "bind" to the controls, rather than you're needing to fill them "manually".


    Cindy Meister, VSTO/Word MVP
    • Marked as answer by ryguy72 Sunday, November 27, 2011 6:06 PM
    Sunday, November 27, 2011 7:14 AM
    Moderator
  • Thanks Cindy!  I just got this working.  I used these references:

    Imports System.Data
    Imports System.Data.OleDb
    Imports ADODB

     

    I also added these references:

     

    I found this to be helpful too:

    http://www.c-sharpcorner.com/UploadFile/mahesh/ADORecSetFromADONet11082005044438AM/ADORecSetFromADONet.aspx

     

     

    Everything works find now!!


    • Edited by ryguy72 Sunday, November 27, 2011 6:10 PM
    Sunday, November 27, 2011 5:59 PM