connecting vb.net and ms access

[arun93]

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]

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

[ambusy]

To create the ODBC database I execute: c:\WINDOWS\SysWOW64\odbcad32.exe
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:

    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:

    <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