Go Premium for a chance to win a PS4. Enter to Win


MS Access and VB.NET creating forms

Posted on 2006-07-05
Medium Priority
Last Modified: 2010-04-23
So i need a little direction to create a form with in VB.net to access a ms access database. Could someone point me in a simple way to doing this? What about making it so that it calculates inventory? If someone already has something in mind in creating a database with inventory items and to take it out with a account number and the qty used, that would be great. Would also like to create reports!
Question by:steverpayne
  • 2

Expert Comment

ID: 17046753
well, its actually pretty easy to do so.  however, youre going to need to study up on ado.net to access your database.  this will give you whatever functionality you need.  for example you can create, delete, and manipulate tables to your hearts content.

check this site out


creating reports however is a little different.  you need software to do that, like crystal reports (and if you have visual studio professional it comes with it)  the only other option ive found is when i used to have to create reports in excel.  that way however is a huge pain.

any other questions let me know.

LVL 27

Accepted Solution

planocz earned 2000 total points
ID: 17047931
Hi steverpayne,

Here is a sample that uses an access DB, Hope it helps....


Imports System.IO
Public Class frmADOToolTip
    Inherits System.Windows.Forms.Form
    Dim sField As String = String.Empty

#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
        PopulateListFromDS(DS, "ProductName")
    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
    Friend WithEvents RadioButton1 As System.Windows.Forms.RadioButton
    Friend WithEvents RadioButton2 As System.Windows.Forms.RadioButton
    Friend WithEvents RadioButton3 As System.Windows.Forms.RadioButton
    <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.RadioButton1 = New System.Windows.Forms.RadioButton
        Me.RadioButton2 = New System.Windows.Forms.RadioButton
        Me.RadioButton3 = New System.Windows.Forms.RadioButton
        Me.lstBoxResults.Location = New System.Drawing.Point(8, 8)
        Me.lstBoxResults.Name = "lstBoxResults"
        Me.lstBoxResults.Size = New System.Drawing.Size(176, 82)
        Me.lstBoxResults.TabIndex = 0
        Me.tipView.SetToolTip(Me.lstBoxResults, "Click to test the tool tip")
        Me.RadioButton1.Location = New System.Drawing.Point(204, 4)
        Me.RadioButton1.Name = "RadioButton1"
        Me.RadioButton1.TabIndex = 1
        Me.RadioButton1.Text = "By Unit Price"
        Me.RadioButton2.Location = New System.Drawing.Point(204, 32)
        Me.RadioButton2.Name = "RadioButton2"
        Me.RadioButton2.Size = New System.Drawing.Size(120, 24)
        Me.RadioButton2.TabIndex = 2
        Me.RadioButton2.Text = "By Units In Stock"
        Me.RadioButton3.Location = New System.Drawing.Point(204, 60)
        Me.RadioButton3.Name = "RadioButton3"
        Me.RadioButton3.Size = New System.Drawing.Size(120, 24)
        Me.RadioButton3.TabIndex = 3
        Me.RadioButton3.Text = "By Product Name"
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(332, 117)
        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 = "D:\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
            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
        Select Case True
            Case RadioButton1.Checked      'By Unit Price
                sCost = DS.Tables(0).Rows(sender.selectedindex)("ProductName").ToString()
                sUnits = DS.Tables(0).Rows(sender.selectedindex)("UnitsInStock").ToString()
                str = "Product Name: " & sCost & vbCr & "Unit In Stock: " & sUnits
            Case RadioButton2.Checked      'By Units In Stock
                sCost = DS.Tables(0).Rows(sender.selectedindex)("ProductName").ToString()
                sUnits = DS.Tables(0).Rows(sender.selectedindex)("Unitprice").ToString()
                str = "Product Name: " & sCost & vbCr & "Cost: " & Format(CDbl(sUnits), "$###.00")
            Case RadioButton3.Checked      'By Product Name
                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
            Case Else
                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
        End Select

        tipView.SetToolTip(Me.lstBoxResults, str)
    End Sub
    Private Sub PopulateListFromDS(ByVal DS As DataSet, ByVal sFieldName As String)
        ' 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)(sFieldName).ToString()
                ' Add product name to the list box
            End If
        Next i
    End Sub
    Private Sub PrintRows(ByVal DS As DataSet)
        Dim i As Integer
        Dim ProductName As String
        Dim SupplierID As String
        Dim CategoryID As String
        Dim UnitPrice As String
        Dim UnitsInStock As String
        Dim UnitsOnOrder As String
        Dim ProductID As String

        Dim objWriter As StreamWriter = New StreamWriter("C:/Products.txt", False)
        objWriter.Write("Products as of " & Today & vbNewLine & vbNewLine)
        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  name for each record.
                ProductName = DS.Tables(0).Rows(i)("ProductName").ToString()
                SupplierID = DS.Tables(0).Rows(i)("SupplierID").ToString()
                CategoryID = DS.Tables(0).Rows(i)("CategoryID").ToString()
                UnitPrice = DS.Tables(0).Rows(i)("UnitPrice").ToString()
                UnitsInStock = DS.Tables(0).Rows(i)("UnitsInStock").ToString()
                UnitsOnOrder = DS.Tables(0).Rows(i)("UnitsOnOrder").ToString()
                ProductID = DS.Tables(0).Rows(i)("ProductID").ToString()
                objWriter.Write("ProductName: " & ProductName & ", Supplier ID: " & SupplierID & vbNewLine)
                objWriter.Write("Category ID: " & CategoryID & vbNewLine)
                objWriter.Write("Unit Price: " & UnitPrice & vbNewLine)
                objWriter.Write("Units In Stock: " & UnitsInStock & ", Units On Order: " & UnitsOnOrder & ", Product ID: " & ProductID & vbNewLine)
            End If
        objWriter = Nothing
    End Sub
    Private Sub RadioButtonChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton1.CheckedChanged, RadioButton2.CheckedChanged, RadioButton3.CheckedChanged
        Select Case sender.text
            Case "By Unit Price"
                sField = "Unitprice"
            Case "By Units In Stock"
                sField = "UnitsInStock"
            Case "By Product Name"
                sField = "ProductName"
        End Select
        PopulateListFromDS(DS, sField)
    End Sub
End Class


Imports System.Data
Imports System.Data.OleDb
Module ModADO
#Region " MY Global Vars "
    'Const adOpenStatic = 3
    'Const adLockOptimistic = 3
    'Const adCmdText = &H1
    '********* 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 ADOAdapter As OleDb.OleDbDataAdapter  '(ADO Access)

    '**** Server Connection Vars *********
    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

    '******* Standard Var's *************
    Public sAppPath As String = System.Windows.Forms.Application.StartupPath
    Public bLogin As Boolean
#End Region
#Region " MY Server Connections "

    '***Sample for a Access Database***
    'User ID=Admin;
    'Data Source=D:\MyDatabase.mdb;
    'Mode=Share Deny None;
    'Extended Properties="";
    'Locale Identifier=1033;
    'Persist Security Info=False;
    'Jet OLEDB:System database=D:\SYSTEM.MDW;
    'Jet OLEDB:Registry Path="";
    'Jet OLEDB:Database Password="";
    'Jet OLEDB:Engine Type=5;         'Type=4 is for Access 95
    'Jet OLEDB:Database Locking Mode=1;
    'Jet OLEDB:Global Partial Bulk Ops=2;
    'Jet OLEDB:Global Bulk Transactions=1;
    'Jet OLEDB:New Database Password="";
    'Jet OLEDB:Create System Database=False;
    'Jet OLEDB:Encrypt Database=False;
    'Jet OLEDB:Don't Copy Locale on Compact=False;
    'Jet OLEDB:Compact Without Replica Repair=False;
    'Jet OLEDB:SFP=False

    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
#End Region
End Module

Author Comment

ID: 17050661
Is there a program with source code already done around? Would Microsoft have it in Vb .net express 2005?

Author Comment

ID: 17051134
Here is more information that maybe needed to keep this simple:

Thursday, July 6, 2006
10:45 AM

Project Name: Inventory Control
Presenter: Steve Payne


The purpose of this Inventory Control is to keep track of the inventory for Cesar A. Caceres, MD PC.

Project Goals

The Project goals are as follows:

Medical Supplies Form

Enter item
Set order level
View inventory levels
Ability to manually adjust inventory levels

Usage Log Sub Form
Track When Items taken with Account number
Qty and by whom

Inventory reorder report

Shows all items at or below reorder level
Generate a report for items that need to be ordered


Visual Basic .Net
Ms Access DB or SQL

Current Status

Related Documents
Documentation will be included in the code
Little documentation for how it works will be done afterwards.

Copyright © 2006 Steve Payne

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Loops Section Overview

772 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