Connect to MS Access db

Posted on 2004-11-30
Last Modified: 2010-04-24
This question has probably been asked a hundred times, but what the heck...

I want to connect to a MS Access DB;
filename="MsgTaker.mdb"; tableName="Msgs"; Coloums="MsgID,MsgFor,MsgFrom,....."
I want to add the data from the textboxes to the db using coding because the user won't be viewing the data just adding to the db.

So I am looking for your help to connect to the database then table and datset and whatever needed cause I'm quite new to VB.NET.
I want to do all this by coding. Please include any references that have to be added.


P.S. I would prefer not to use SQL if that matter, probably JET or something instead.
Question by:Hurb
    LVL 24

    Expert Comment

    by:Jeff Certain
    1. You can't use SQL to Access an Access DB. Instead, you need the OLEDB objects. Add Imports System.Data.OleDB to your class.

    2. The addition must be done using a SQL INSERT statement to add new records (UPDATE to update an existing record).

    3. Your code needs to look something like this:

                Dim conn AsoledbConnection = New OLEDBConnection(connectionString)
                Dim cmd As oledbCommand = "INSERT INTO tableName(columnA,columnB,...) VALUES (Value1,Value2,...)"

    You need to list the values to insert in exaclty the order that the columns are listed. Autonumber columns do not need to be listed. Also, ensure that all mandatory fields have values provided.
    LVL 2

    Author Comment

    How do i go about getting the connectionString? Probably a Basic Question but sorry.

    What about datasets and all that crap.
    LVL 27

    Assisted Solution

    Here is a sample of a ADO ToolTip that I made...

    'FORM 1
    Public Class frmADOToolTip
        Inherits System.Windows.Forms.Form

    #Region " Windows Form Designer generated code "

        Public Sub New()

            'This call is required by the Windows Form Designer.

            'Add any initialization after the InitializeComponent() call

        End Sub

        'Form overrides dispose to clean up the component list.
        Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
            If disposing Then
                If Not (components Is Nothing) Then
                End If
            End If
        End Sub

        'Required by the Windows Form Designer
        Private components As System.ComponentModel.IContainer

        'NOTE: The following procedure is required by the Windows Form Designer
        'It can be modified using the Windows Form Designer.  
        'Do not modify it using the code editor.
        Friend WithEvents tipView As System.Windows.Forms.ToolTip
        Friend WithEvents lstBoxResults As System.Windows.Forms.ListBox
        <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
            Me.components = New System.ComponentModel.Container
            Me.lstBoxResults = New System.Windows.Forms.ListBox
            Me.tipView = New System.Windows.Forms.ToolTip(Me.components)
            Me.lstBoxResults.Location = New System.Drawing.Point(28, 32)
            Me.lstBoxResults.Name = "lstBoxResults"
            Me.lstBoxResults.Size = New System.Drawing.Size(176, 160)
            Me.lstBoxResults.TabIndex = 0
            Me.tipView.SetToolTip(Me.lstBoxResults, "Click to test the tool tip")
            Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
            Me.ClientSize = New System.Drawing.Size(228, 209)
            Me.Name = "frmADOToolTip"
            Me.Text = "ADO ToolTip Test"

        End Sub

    #End Region
        '/ <summary>
        '/ The main entry point for the application.
        '/ </summary>
        <STAThread()> Public Shared Sub Main()
            Application.Run(New frmADOToolTip)
        End Sub
        Private Sub InitializeData()

            Cursor.Current = Cursors.WaitCursor
            sDBName = "C:\Northwind.mdb"       '<---- A standard access database from Microsoft
            sTableName = "Products"


            sSql = ""
            sSql = "SELECT * "
            sSql += "FROM " & sTableName & " "
            sSql += "ORDER BY ProductName"
                'TblAdapter = New SqlDataAdapter(sSql, Connect)            'FOR SQL Server
                'TblAdapter = New Odbc.OdbcDataAdapter(sSQL, SyConnect)    'FOR SyBase Server
                ADOAdapter = New OleDb.OleDbDataAdapter(sSql, Con)         'FOR ADO Access database

            Catch Exp As Data.OleDb.OleDbException
                MsgBox("FillDataSet Procedure Error", MsgBoxStyle.Critical, "Load Report Error")
            Catch Exp As Exception
                MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
            End Try
            Cursor.Current = Cursors.Default
        End Sub
        Private Sub lstboxResults_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstBoxResults.Click
            Dim sCost As String
            Dim sUnits As String
            Dim str As String
            sCost = DS.Tables(0).Rows(sender.selectedindex)("Unitprice").ToString()
            sUnits = DS.Tables(0).Rows(sender.selectedindex)("UnitsInStock").ToString()
            str = "Cost: " & Format(CDbl(sCost), "$###.00") & vbCr & "Unit In Stock: " & sUnits

            tipView.SetToolTip(Me.lstBoxResults, str)
        End Sub
        Private Sub PopulateListFromDS(ByVal DS)
            ' A table in a dataset is used to populate a list box with
            ' product name from the products table.
            ' With the dataset the name of the table and field must be known
            ' at design time. If they are misspelled or mistyped
            ' an error will be generated only at runtime.

            Dim s As String
            Dim i As Integer


            For i = 0 To DS.Tables(0).Rows.Count - 1
                ' Check to see if row is flagged as deleted.
                If Not DS.Tables(0).Rows(i).RowState = DataRowState.Deleted Then
                    ' Get the product name for each record.
                    s = DS.Tables(0).Rows(i)("ProductName").ToString()
                    ' Add product name to the list box
                End If
            Next i
        End Sub
    End Class


    Imports System.IO
    Imports ADODB
    Imports System.Data
    Imports System.Data.SqlClient
    Module ModADO
    #Region " MY Global Vars "
        '********* database Var's ************
        Public sSql As String
        Public sTableName As String
        Public iCounter As Short
        Public myDataRow As DataRow
        Public DS As New DataSet
        Public TblAdapter As Odbc.OdbcDataAdapter   '(sSql or SyConnect)
        Public ADOAdapter As OleDb.OleDbDataAdapter  '(ADO)

        '**** Server Connection Vars *********Imports System.Data.SqlClient

        Public sDBServer As String               '<--- Server Name
        Public sUSERid As String                 '<--- User Id
        Public sDBName As String                 '<--- Database Name
        Public sDBPass As String                 '<--- Password
        Public Con As OleDb.OleDbConnection      '<--- for Access
        Public Connect As SqlConnection          '<--- for msSQL
        Public SyConnect As Odbc.OdbcConnection   '<---- for Sybase

        '******* Standard Var's *************
        Public sAppPath As String = Application.StartupPath
        Public bLogin As Boolean
    #End Region
    #Region " MY Server Connections "
        Public Sub OpenDBADOConnection()
                Con = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBName & ";password=" & sDBPass & ";")
            Catch Ex As Exception
                MsgBox("Exception: " & Ex.Message & "  " & Ex.ToString, MsgBoxStyle.Critical)
                Cursor.Current = Cursors.Default
            End Try
        End Sub
        Public Sub OpenDBSqlConnection()
            If Len(sDBPass) = 0 Then
                sDBPass = ""
            End If
                Connect = New SqlConnection("Initial Catalog=" & sDBName & ";Data Source=" & sDBServer & ";User ID=" & sUSERid & ";password=" & sDBPass & ";")
            Catch Ex As Exception
                MsgBox("Exception: " & Ex.Message & "  " & Ex.ToString, MsgBoxStyle.Critical)
                Cursor.Current = Cursors.Default
            End Try
        End Sub

    #End Region

    End Module
    LVL 24

    Expert Comment

    by:Jeff Certain
    If all you're doing is adding records to the database, you can do this directly, without datasets/datareaders/dataadapters/etc. You just need a connection and the SQL string to tell the server (i.e. the Access database) what to do...

    For Access databases, the connection string looks like this
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\path\databaseName.mdb"

    Note that this is not a password protected database in this example.
    LVL 2

    Author Comment


    What Sub do i call to add to the database then.
    LVL 24

    Accepted Solution

    You must have some sort of submit button on your form. In the onClick event for the button, add this code:

    Dim conn As oledbConnection = New OLEDBConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\path\databaseName.mdb")
    dim strSQL as string
    '***** Insert the appropriate field names and values from textBoxA.text into the string
    strsql="INSERT INTO tableName(columnA,columnB,...) VALUES (" & textBoxA.text & "," & textBoxB.Text & ")"
    Dim cmd As oledbCommand = new Oledbcommand(strsql,conn)
    LVL 2

    Author Comment

    What reference do i have to add cause:
    Type 'oledbConnection' is not defined.
    Type 'oledbCommand' is not defined.
    LVL 24

    Expert Comment

    by:Jeff Certain
    you need to add the line Imports System.Data.OleDb (or something close to this -- intellisense will let you know) at the beginning of the class, before the class declaration....see the very first thing I posted (#1 in the first response to your post).

    Imports System.Data.Oledb

    Public Class MyClass
    LVL 2

    Author Comment

    Just more errors, is there a reference i must add like from the project menu, add reference.
    Could you check your solution explorer for the references that you have there please.

    Thanks again.
    LVL 27

    Expert Comment

    All the references are in my code above
    LVL 24

    Expert Comment

    by:Jeff Certain
    Reference System.Data

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
    It’s quite interesting for me as I worked with Excel using for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now