Connect to MS Access db

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeff CertainCommented:
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.
HurbAuthor Commented:
How do i go about getting the connectionString? Probably a Basic Question but sorry.

What about datasets and all that crap.
Here is a sample of a ADO ToolTip that I made...

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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Jeff CertainCommented:
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.
HurbAuthor Commented:

What Sub do i call to add to the database then.
Jeff CertainCommented:
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)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HurbAuthor Commented:
What reference do i have to add cause:
Type 'oledbConnection' is not defined.
Type 'oledbCommand' is not defined.
Jeff CertainCommented:
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
HurbAuthor Commented:
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.
All the references are in my code above
Jeff CertainCommented:
Reference System.Data
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.