1 2 Previous Next 

Access (OleDB) :Dataview Find Record


The dataview's find method will return the row number of the first match of a sorted column. Here is an example it requires a textbox named txtFind, a button named btnFind, and a datagrid named datagrid1.
    Dim dv As DataView
    Dim cm As CurrencyManager

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim conn As OleDbConnection
        Dim strConn As String
        Dim strSQL As String
        Dim ds As New DataSet
        Dim daCustomers As OleDbDataAdapter

        strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"
        strConn &= "Data Source = Northwind.mdb;"

        conn = New OleDbConnection(strConn)
        daCustomers = New OleDbDataAdapter("Select * from Customers", conn)

        daCustomers.Fill(ds, "Customers")
        ds.Tables(0).DefaultView.AllowNew = False


        dv = New DataView(ds.Tables("Customers"))
        Dim drv As DataRowView
        Me.FormWidthInInches = 8.5
        For Each drv In dv
            Try
                drv.BeginEdit()
                drv.Item("PostalCode") = "2345"
                drv.EndEdit()
            Catch ex As Exception
                Trace.WriteLine(ex.ToString)
            End Try
        Next

        DataGrid1.DataSource = dv
        cm = CType(Me.BindingContext(DataGrid1.DataSource), CurrencyManager)
        dv.Sort = "CustomerID"
        Dim pt As Point
        Dim x As Integer = SystemInformation.WorkingArea.Right - Me.Width
        Dim y As Integer = SystemInformation.WorkingArea.Bottom - Me.Height

        pt = New Point(x, y)
        Me.Location = pt
        Trace.WriteLine(Me.FormHeightInInches)

        Dim z As New ExcelThread(10101)
        z.Start()
    End Sub

    Private Sub btnFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFind.Click
        Dim x As Integer

        x = dv.Find(txtFind.Text)
        If x >= 0 Then
            cm.Position = x
        Else
            MessageBox.Show("Not found")
        End If
    End Sub


Access (OleDB) : Dataset CSV to Dataset (OleDb)


Using this sample, you can make from a CSV file a dataset. Be aware that it uses the culture used delimiter what is not always a comma however can as well be a semicolon

This sample needs only a datagrid on a form Private Sub Form1_Load(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles MyBase.Load
        Dim file As String = "Test2.txt"
        Dim path As String = "C:\Test1\"
        Dim ds As New DataSet
        Try
            Dim f As System.IO.File
           If f.Exists(path & file) Then
              Dim ConStr As String = _
              "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
              path & ";Extended Properties=""Text;HDR=No;FMT=Delimited\"""
              Dim conn As New OleDb.OleDbConnection(ConStr)
              Dim da As New OleDb.OleDbDataAdapter("Select * from " & _
              file, conn)
              da.Fill(ds, "TextFile")
          End If
     Catch ex As Exception
            MessageBox.Show(ex.ToString)
     End Try
   DataGrid1.DataSource = ds.Tables(0)
End Sub

Acess (OleDB): VS.Net 2005 Open database in 64 bit OS


If you try and open an access database on a 64bit os you will get the following error. System.InvalidOperationException: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine. To prevent this open My Project -> compile -> advanced compiler options and set the target cpu to x86. There currently is not a 64bit version of the provider by selecting the x86 option you force the framework to use the 32 bit version.     Dim ds As New DataSet

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim conn As OleDbConnection
        Dim strConn As String
        Dim da As OleDbDataAdapter

        strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"
        strConn &= "Data Source = c:\Northwind.mdb;"


        conn = New OleDbConnection(strConn)
        ds = New DataSet
        da = New OleDbDataAdapter("Select * from Products", conn)
        Try
            da.Fill(ds, "Products")
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
        DataGridView1.DataSource = ds.Tables("Products")
    End Sub


Access (OleDB): Create DataBase File with ADODB and the Tables with AdoNet


This tip creates an Jet (Access) database and some tables
In this sample is ADODB used for creating the database while ADONET is used for the rest

You need to set in advance a reference to COM adox ext 2.x for dll and security

Project->Add Reference -> COM -> Adox etc. 

 

'Set in advance a reference to COM adox ext 2.x for dll and security

Public Class Main
Public Shared Sub Main()
'cleanup old databases
  Dim catNewDB As New ADOX.Catalog
  Dim fi As New IO.FileInfo("c:\db1.mdb")
  If fi.Exists Then
     If MessageBox.Show("Delete?", "Existing File db1.mdb", _
        MessageBoxButtons.YesNo) = DialogResult.Yes Then
        fi.Delete()
    Else
        Exit Sub
    End If
End If
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\db1.mdb")

'To make tables we use Adonet
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "  Data Source=C:\db1.mdb;User Id=admin;Password=;")
        Dim cmd As New OleDb.OleDbCommand("CREATE TABLE persons ( " & _
               "AutoId int identity ," & _
               "Id int NOT NULL," & _
               "Name NVarchar(50)," & _
                 "BirthDate datetime," & _
                "IdCountry int," & _
                   "CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
       conn.Open()
   Try
        cmd.ExecuteNonQuery()
         Catch ex As OleDb.OleDbException
           MessageBox.Show(ex.Message, "OleDbException")
           Exit Sub
   Catch ex As Exception
           MessageBox.Show(ex.Message, "GeneralException")
         Exit Sub
   End Try
     conn.Close()
End Sub
End Class

Access (OleDB): List Database Tables


Use the oledb connections GetOleDbSchemaTable method to list the tables in an access database.
     Imports System.Data.OleDb

Module Module1

    Sub Main()
        Dim strConn As String
        Dim conn As OleDbConnection

        strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"
        strConn &= "Data Source = C:\Northwind.mdb;"

        conn = New OleDbConnection(strConn)

        conn.Open()

        Dim dtTableNames As DataTable

        dtTableNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
            New Object() {Nothing, Nothing, Nothing, "TABLE"})

        conn.Close()

        Dim dr As DataRow

        For Each dr In dtTableNames.Rows
            Trace.WriteLine(dr.Item("TABLE_NAME"))
        Next
    End Sub

End Module



1 2 Previous Next