connecting vb.net and ms access

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • arun93
    New Member
    • Jun 2014
    • 1

    connecting vb.net and ms access

    hi sir,
    i've a problem during odbc connection with vb.net and ms access
    pls help me
    send me the query syntax for insert, update, select, delete in vb.net to connect ms access using odbc connection
  • manageknowledge
    New Member
    • Jun 2014
    • 10

    #2
    You need to first create a database source using control panel and then add it to your VB.NET project.

    Comment

    • ambusy
      New Member
      • Aug 2014
      • 5

      #3
      To create the ODBC database I execute: c:\WINDOWS\SysW OW64\odbcad32.e xe
      In the resulting window, on tab "User DSN"
      - I add my database with name ArCatSy with attributes Microsoft Access Driver (*.mdb)
      - Save and exit
      In Visual basic I have this (partial) code:
      Code:
          Dim DbConn As odbcConnection
          Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
              SqlProv = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=ArCatSy.mdb"
              DbConn = New odbcConnection(SqlProv)
              ' Check db exists
              Dim retr As MsgBoxResult
              retr = MsgBoxResult.Retry
              While retr = MsgBoxResult.Retry
                  Try
                      DbConn.Open()
                      DbConn.Close()
                      retr = MsgBoxResult.Ok
                  Catch ex As Exception
                      retr = MsgBox("No ArCatSy database?", MsgBoxStyle.RetryCancel)
                  End Try
              End While
              If retr = MsgBoxResult.Cancel Then
                  Me.Close()
                  Exit Sub
              End If
          End Sub
          Private Sub OpenDb()
              Try
                  DbConn.Open()
              Catch ex As Exception
                  MsgBox(ex.ToString(), , "Open DB")
              End Try
          End Sub
          ' put all PrtSpec's in a TreeNode
          Private sub procSpecs
              OpenDb()
              Dim cSelectSQL As String = "SELECT PrtSpec FROM PrtSpecs WHERE CollCode = ? ORDER BY PrtSpec"
              Dim DRcSelect As odbcDataReader = Nothing
              Dim DbCcSelect As odbcCommand
              Dim cSelP1 As New odbcParameter("@CollCode", odbc.odbcType.VarChar, 4)
              DbCcSelect = New odbcCommand(cSelectSQL, DbConn)
              DbCcSelect.Parameters.Add(cSelP1)
              Try ' all prtspecs of my Collection
                  cSelP1.Value = CollCode
                  DRcSelect = DbCcSelect.ExecuteReader()
                  Do While (DRcSelect.Read())
                      Dim prt As String = GetDbStringValue(DRcSelect, 0)
                      PrtNode = New TreeNode(prt)
                      PrtNode.Name = prt
                      PrtNode.Checked = (prt = defPrt)
                      CollCodeNode.Nodes.Add(PrtNode)
                  Loop
                  CollCodeNode.Expand()
              Catch ex As Exception
                  MsgBox(ex.ToString())
               Finally
                  If Not (DRcSelect Is Nothing) Then
                     DRcSelect.Close()
                  End If
               End Try
               DbConn.Close()
          End Sub
          ' insert some row
          Private Sub InsRow(toData as TwInDb)
              Dim SQLTwInsert As String = "INSERT INTO SearchTerms (CollCode, CollSeq, TermType, TermText) VALUES (?, ?, ?, ?)"
              Dim DRTwInsert As odbcDataReader = Nothing
              Dim DCCTwInsert As odbcCommand
              Dim TwInsP1 As New odbcParameter("@CollCode", odbc.odbcType.VarChar, 4)
              Dim TwInsP2 As New odbcParameter("@CollSeq", Odbc.OdbcType.Int, 4)
              Dim TwInsP3 As New odbcParameter("@TermType", odbc.odbcType.VarChar, 4)
              Dim TwInsP4 As New odbcParameter("@TermText", odbc.odbcType.VarChar, 255)
              DCCTwInsert = New odbcCommand(SQLTwInsert, DbConn)
              DCCTwInsert.Parameters.Add(TwInsP1)
              DCCTwInsert.Parameters.Add(TwInsP2)
              DCCTwInsert.Parameters.Add(TwInsP3)
              DCCTwInsert.Parameters.Add(TwInsP4)
              OpenDb()
              TwInsP1.Value = CurCollCode
              TwInsP2.Value = CollSeq
              TwInsP3.Value = toData.FldTermTypec
              TwInsP4.Value = toData.FldTermTypet
              Try
                  DRTwInsert = DCCTwInsert.ExecuteReader()
              Catch ex As Exception
                  MsgBox(ex.ToString(), , "Insert Term")
              Finally
                  If Not (DRTwInsert Is Nothing) Then
                      DRTwInsert.Close()
                  End If
              End Try
              DbConn.Close()
          End Sub
      For eac h type of data I have a function to retrieve the nth element of a cursorrow in it's correct format taking care of NULLs. 2 examples:
      Code:
          <Global.System.Diagnostics.DebuggerStepThroughAttribute()> _
          Friend Function GetDbStringValue(ByVal Dr As odbcDataReader, ByVal nr As Integer) As String
              If IsDBNull(Dr.Item(nr)) Then
                  Return ""
              Else
                  Return Dr.GetString(nr).TrimEnd
              End If
          End Function
      
          <Global.System.Diagnostics.DebuggerStepThroughAttribute()> _
          Friend Function GetDbBooleanValue(ByVal Dr As odbcDataReader, ByVal nr As Integer) As Boolean
              If IsDBNull(Dr.Item(nr)) Then
                  Return False
              Else
                  Return Dr.GetBoolean(nr)
              End If
          End Function
      with a GET and an UPDATE example, you can figure out how to solve your code problems. Good Luck
      Last edited by ambusy; Aug 18 '14, 07:40 PM. Reason: forgot some routines

      Comment

      Working...