Retrieving data from MSAccess Database from VB.NET

Question

I want to retrieve data from the database. I am using MSAccess as a database. The problem I am facing here is that I am not able to fetch Data from MSAccess through vb.net.

Find Below code snippet that I am using.

Imports System.Data.OleDb
Public Class Form1
    Dim cn As OleDb.OleDbConnection
    Dim ds As DataSet
    Dim da As OleDbDataAdapter
    Dim tables As DataTableCollection
    Dim source1 As New BindingSource
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim connectionString As String

        connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                "Data Source=C:\myfolder\fdfd.accdb;"

        cn = New OleDbConnection

        cn.ConnectionString = connectionString


        ds = New DataSet
        tables = ds.Tables
        da = New OleDbDataAdapter("Select * From names", cn)
        da.Fill(ds, "names")


        Dim view As New DataView(tables(0))
        source1.DataSource = view
        DataGridView1.DataSource = view
    End Sub
End Class

Error occurs at line:da.Fill(ds, "names")

Here is error screenshot :

Error Screenshot

Thanks in Advance

Cody Gray · Accepted Answer · 2017-09-25 09:10:30Z

Based on equisde's answer, I have come up with the following code that works for me:

Imports System.Data.OleDb
Public Class Form1
    Dim cn As OleDb.OleDbConnection
    Dim ds As DataSet
    Dim da As OleDbDataAdapter
    Dim tables As DataTableCollection
    Dim source1 As New BindingSource
    Dim oleCommand As OleDbCommand
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load 
       cn = New OleDbConnection

        If System.IO.File.Exists("C:\myfolder\fdfd.accdb") Then
            cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myfolder\fdfd.accdb; Persist Security Info=False;"
        End If
        Try
            Dim SQL As String = "SELECT * from [names]"

            oleCommand = New OleDbCommand(SQL, cn)
            da = New OleDbDataAdapter(oleCommand)
            Dim table = New DataTable("[names]")
            cn.Open()
            da.Fill(table)
            cn.Close()
            Dim view As New DataView(table)
            source1.DataSource = view
            DataGridView1.DataSource = view

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
End Class

ɐsɹǝʌ ǝɔıʌ · Accepted Answer · 2017-09-22 10:06:01Z

0

Try to open your connection before filling the dataset

Imports System.Data.OleDb

Public Class Form1

Dim cn As OleDb.OleDbConnection
Dim ds As DataSet
Dim da As OleDbDataAdapter
Dim tables As DataTableCollection
Dim source1 As New BindingSource

Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load

    cn = New OleDbConnection

    cn.ConnectionString =  "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myfolder\fdfd.accdb; Persist Security Info=False;"

    Try

        ds = New DataSet
        tables = ds.Tables
        cn.Open()
        da = New OleDbDataAdapter("SELECT * FROM [Names]", cn)
        da.Fill(ds, "names")
        cn.Close()
        Dim view As New DataView(tables(0))
        source1.DataSource = view
        DataGridView1.DataSource = view

    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

End Sub
End Class

edited Sep 22, 2017 at 10:06

answered Sep 22, 2017 at 9:24

ɐsɹǝʌ ǝɔıʌ

4,5223 gold badges38 silver badges59 bronze badges

6 Comments

RBK Over a year ago

I have tried out a snippet that you have suggested, but same error occurred. Is there any other way that I can access the data from MSAccess ??

ɐsɹǝʌ ǝɔıʌ Over a year ago

The code is correct. Try to use the try/catch block to catch exceptions. See edited code. Maybe your database is missing or your connection string is wrong.

RBK Over a year ago

the connection string is correct, but don't know why this error occurs and database is also present

ɐsɹǝʌ ǝɔıʌ Over a year ago

Using try/catch block will clear up your doubts. It will show you the detailed error message into a dialog box.

RBK Over a year ago

IErrorInfo.GetDescription failed with E_FAIL(0x80004005). following is messagebox I got.

|

Collectives™ on Stack Overflow

Retrieving data from MSAccess Database from VB.NET

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related