By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,899 Members | 1,230 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,899 IT Pros & Developers. It's quick & easy.

connecting vb.net and ms access

P: 1
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
Jun 22 '14 #1
Share this Question
Share on Google+
2 Replies


P: 10
You need to first create a database source using control panel and then add it to your VB.NET project.
Jun 22 '14 #2

P: 5
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:
Expand|Select|Wrap|Line Numbers
  1.     Dim DbConn As odbcConnection
  2.     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  3.         SqlProv = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=ArCatSy.mdb"
  4.         DbConn = New odbcConnection(SqlProv)
  5.         ' Check db exists
  6.         Dim retr As MsgBoxResult
  7.         retr = MsgBoxResult.Retry
  8.         While retr = MsgBoxResult.Retry
  9.             Try
  10.                 DbConn.Open()
  11.                 DbConn.Close()
  12.                 retr = MsgBoxResult.Ok
  13.             Catch ex As Exception
  14.                 retr = MsgBox("No ArCatSy database?", MsgBoxStyle.RetryCancel)
  15.             End Try
  16.         End While
  17.         If retr = MsgBoxResult.Cancel Then
  18.             Me.Close()
  19.             Exit Sub
  20.         End If
  21.     End Sub
  22.     Private Sub OpenDb()
  23.         Try
  24.             DbConn.Open()
  25.         Catch ex As Exception
  26.             MsgBox(ex.ToString(), , "Open DB")
  27.         End Try
  28.     End Sub
  29.     ' put all PrtSpec's in a TreeNode
  30.     Private sub procSpecs
  31.         OpenDb()
  32.         Dim cSelectSQL As String = "SELECT PrtSpec FROM PrtSpecs WHERE CollCode = ? ORDER BY PrtSpec"
  33.         Dim DRcSelect As odbcDataReader = Nothing
  34.         Dim DbCcSelect As odbcCommand
  35.         Dim cSelP1 As New odbcParameter("@CollCode", odbc.odbcType.VarChar, 4)
  36.         DbCcSelect = New odbcCommand(cSelectSQL, DbConn)
  37.         DbCcSelect.Parameters.Add(cSelP1)
  38.         Try ' all prtspecs of my Collection
  39.             cSelP1.Value = CollCode
  40.             DRcSelect = DbCcSelect.ExecuteReader()
  41.             Do While (DRcSelect.Read())
  42.                 Dim prt As String = GetDbStringValue(DRcSelect, 0)
  43.                 PrtNode = New TreeNode(prt)
  44.                 PrtNode.Name = prt
  45.                 PrtNode.Checked = (prt = defPrt)
  46.                 CollCodeNode.Nodes.Add(PrtNode)
  47.             Loop
  48.             CollCodeNode.Expand()
  49.         Catch ex As Exception
  50.             MsgBox(ex.ToString())
  51.          Finally
  52.             If Not (DRcSelect Is Nothing) Then
  53.                DRcSelect.Close()
  54.             End If
  55.          End Try
  56.          DbConn.Close()
  57.     End Sub
  58.     ' insert some row
  59.     Private Sub InsRow(toData as TwInDb)
  60.         Dim SQLTwInsert As String = "INSERT INTO SearchTerms (CollCode, CollSeq, TermType, TermText) VALUES (?, ?, ?, ?)"
  61.         Dim DRTwInsert As odbcDataReader = Nothing
  62.         Dim DCCTwInsert As odbcCommand
  63.         Dim TwInsP1 As New odbcParameter("@CollCode", odbc.odbcType.VarChar, 4)
  64.         Dim TwInsP2 As New odbcParameter("@CollSeq", Odbc.OdbcType.Int, 4)
  65.         Dim TwInsP3 As New odbcParameter("@TermType", odbc.odbcType.VarChar, 4)
  66.         Dim TwInsP4 As New odbcParameter("@TermText", odbc.odbcType.VarChar, 255)
  67.         DCCTwInsert = New odbcCommand(SQLTwInsert, DbConn)
  68.         DCCTwInsert.Parameters.Add(TwInsP1)
  69.         DCCTwInsert.Parameters.Add(TwInsP2)
  70.         DCCTwInsert.Parameters.Add(TwInsP3)
  71.         DCCTwInsert.Parameters.Add(TwInsP4)
  72.         OpenDb()
  73.         TwInsP1.Value = CurCollCode
  74.         TwInsP2.Value = CollSeq
  75.         TwInsP3.Value = toData.FldTermTypec
  76.         TwInsP4.Value = toData.FldTermTypet
  77.         Try
  78.             DRTwInsert = DCCTwInsert.ExecuteReader()
  79.         Catch ex As Exception
  80.             MsgBox(ex.ToString(), , "Insert Term")
  81.         Finally
  82.             If Not (DRTwInsert Is Nothing) Then
  83.                 DRTwInsert.Close()
  84.             End If
  85.         End Try
  86.         DbConn.Close()
  87.     End Sub
  88.  
  89.  
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:
Expand|Select|Wrap|Line Numbers
  1.     <Global.System.Diagnostics.DebuggerStepThroughAttribute()> _
  2.     Friend Function GetDbStringValue(ByVal Dr As odbcDataReader, ByVal nr As Integer) As String
  3.         If IsDBNull(Dr.Item(nr)) Then
  4.             Return ""
  5.         Else
  6.             Return Dr.GetString(nr).TrimEnd
  7.         End If
  8.     End Function
  9.  
  10.     <Global.System.Diagnostics.DebuggerStepThroughAttribute()> _
  11.     Friend Function GetDbBooleanValue(ByVal Dr As odbcDataReader, ByVal nr As Integer) As Boolean
  12.         If IsDBNull(Dr.Item(nr)) Then
  13.             Return False
  14.         Else
  15.             Return Dr.GetBoolean(nr)
  16.         End If
  17.     End Function
  18.  
with a GET and an UPDATE example, you can figure out how to solve your code problems. Good Luck
Aug 18 '14 #3

Post your reply

Sign in to post your reply or Sign up for a free account.