Connect to MS Access db

Posted on 2004-11-30
Medium Priority
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
  • 5
  • 4
  • 2
LVL 24

Expert Comment

by:Jeff Certain
ID: 12709568
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.

Author Comment

ID: 12709601
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

planocz earned 400 total points
ID: 12709636
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 24

Expert Comment

by:Jeff Certain
ID: 12709653
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.

Author Comment

ID: 12709705

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

Accepted Solution

Jeff Certain earned 400 total points
ID: 12709761
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)

Author Comment

ID: 12709897
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
ID: 12709915
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

Author Comment

ID: 12709986
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

ID: 12710060
All the references are in my code above
LVL 24

Expert Comment

by:Jeff Certain
ID: 12710065
Reference System.Data

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses
Course of the Month14 days, 12 hours left to enroll

840 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