Public Class Form1
    '
    'This is a generic Access database search routine that will accept as input:
    '  1.  The name of an Access .MDB file
    '  2.  The name of a table in that database
    '  3.  A string to find in the database
    '
    'The program will then display a data grid of ALL rows where that search string was located in ANY column
    'If the search string was not found an appropriate message will be displayed in place of the data grid
    '
    '
    Inherits System.Windows.Forms.Form
    Dim strAccessFileName As String 'The name of the access database
    Dim blnAccessFileOpened As Boolean 'Did we successfully open the file?
    Dim blnNewSearchTable As Boolean 'We flag if the table has changed and we need to clear our data table in memory and get a new copy from disk

    'OLE objects:
    Dim conDBConnection As New OleDb.OleDbConnection
    Dim odbaAdapter As New OleDb.OleDbDataAdapter
    Dim odbcmdSelect As New OleDb.OleDbCommand

    'We want two data tables:
    Dim dtAllContents As New DataTable 'This will contain the entire table from the Access database so we can search for our string
    Dim dtDisplayContents As New DataTable 'This will contain all rows where the string was found - this will be displayed in the data grid

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    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
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    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 GroupBox1 As System.Windows.Forms.GroupBox
    Friend WithEvents MainMenu1 As System.Windows.Forms.MainMenu
    Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
    Friend WithEvents MenuItem1 As System.Windows.Forms.MenuItem
    Friend WithEvents MenuItem3 As System.Windows.Forms.MenuItem
    Friend WithEvents Label1 As System.Windows.Forms.Label
    Friend WithEvents Label2 As System.Windows.Forms.Label
    Friend WithEvents Label4 As System.Windows.Forms.Label
    Friend WithEvents chkCaseSensitive As System.Windows.Forms.CheckBox
    Friend WithEvents cmbTableList As System.Windows.Forms.ComboBox
    Friend WithEvents btnSpecifyFile As System.Windows.Forms.Button
    Friend WithEvents lblTableToSearch As System.Windows.Forms.Label
    Friend WithEvents btnSearch As System.Windows.Forms.Button
    Friend WithEvents txtSearchString As System.Windows.Forms.TextBox
    Friend WithEvents lblAccessFileName As System.Windows.Forms.Label
    Friend WithEvents txtOutput As System.Windows.Forms.TextBox
    Friend WithEvents lblNotFound As System.Windows.Forms.Label
    Friend WithEvents OpenFileDialog1 As System.Windows.Forms.OpenFileDialog
    Friend WithEvents lblStatus As System.Windows.Forms.Label
    Friend WithEvents mnuFileOpen As System.Windows.Forms.MenuItem
    Friend WithEvents mnuExit As System.Windows.Forms.MenuItem
    Friend WithEvents mnuUsage As System.Windows.Forms.MenuItem
    Friend WithEvents mnuHistory As System.Windows.Forms.MenuItem
    Friend WithEvents mnuAbout As System.Windows.Forms.MenuItem
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Dim resources As System.Resources.ResourceManager = New System.Resources.ResourceManager(GetType(Form1))
        Me.GroupBox1 = New System.Windows.Forms.GroupBox
        Me.lblStatus = New System.Windows.Forms.Label
        Me.btnSearch = New System.Windows.Forms.Button
        Me.chkCaseSensitive = New System.Windows.Forms.CheckBox
        Me.cmbTableList = New System.Windows.Forms.ComboBox
        Me.txtSearchString = New System.Windows.Forms.TextBox
        Me.btnSpecifyFile = New System.Windows.Forms.Button
        Me.lblAccessFileName = New System.Windows.Forms.Label
        Me.Label2 = New System.Windows.Forms.Label
        Me.Label1 = New System.Windows.Forms.Label
        Me.lblTableToSearch = New System.Windows.Forms.Label
        Me.Label4 = New System.Windows.Forms.Label
        Me.MainMenu1 = New System.Windows.Forms.MainMenu
        Me.MenuItem1 = New System.Windows.Forms.MenuItem
        Me.mnuFileOpen = New System.Windows.Forms.MenuItem
        Me.mnuExit = New System.Windows.Forms.MenuItem
        Me.MenuItem3 = New System.Windows.Forms.MenuItem
        Me.mnuUsage = New System.Windows.Forms.MenuItem
        Me.mnuHistory = New System.Windows.Forms.MenuItem
        Me.mnuAbout = New System.Windows.Forms.MenuItem
        Me.DataGrid1 = New System.Windows.Forms.DataGrid
        Me.txtOutput = New System.Windows.Forms.TextBox
        Me.lblNotFound = New System.Windows.Forms.Label
        Me.OpenFileDialog1 = New System.Windows.Forms.OpenFileDialog
        Me.GroupBox1.SuspendLayout()
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'GroupBox1
        '
        Me.GroupBox1.Controls.Add(Me.lblStatus)
        Me.GroupBox1.Controls.Add(Me.btnSearch)
        Me.GroupBox1.Controls.Add(Me.chkCaseSensitive)
        Me.GroupBox1.Controls.Add(Me.cmbTableList)
        Me.GroupBox1.Controls.Add(Me.txtSearchString)
        Me.GroupBox1.Controls.Add(Me.btnSpecifyFile)
        Me.GroupBox1.Controls.Add(Me.lblAccessFileName)
        Me.GroupBox1.Controls.Add(Me.Label2)
        Me.GroupBox1.Controls.Add(Me.Label1)
        Me.GroupBox1.Controls.Add(Me.lblTableToSearch)
        Me.GroupBox1.Controls.Add(Me.Label4)
        Me.GroupBox1.Font = New System.Drawing.Font("Microsoft Sans Serif", 11.25!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.GroupBox1.Location = New System.Drawing.Point(8, 0)
        Me.GroupBox1.Name = "GroupBox1"
        Me.GroupBox1.Size = New System.Drawing.Size(640, 184)
        Me.GroupBox1.TabIndex = 0
        Me.GroupBox1.TabStop = False
        '
        'lblStatus
        '
        Me.lblStatus.BackColor = System.Drawing.Color.FromArgb(CType(192, Byte), CType(255, Byte), CType(255, Byte))
        Me.lblStatus.BorderStyle = System.Windows.Forms.BorderStyle.Fixed3D
        Me.lblStatus.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblStatus.Location = New System.Drawing.Point(336, 120)
        Me.lblStatus.Name = "lblStatus"
        Me.lblStatus.Size = New System.Drawing.Size(296, 23)
        Me.lblStatus.TabIndex = 8
        '
        'btnSearch
        '
        Me.btnSearch.Location = New System.Drawing.Point(544, 152)
        Me.btnSearch.Name = "btnSearch"
        Me.btnSearch.TabIndex = 7
        Me.btnSearch.Text = "Search"
        '
        'chkCaseSensitive
        '
        Me.chkCaseSensitive.Location = New System.Drawing.Point(336, 152)
        Me.chkCaseSensitive.Name = "chkCaseSensitive"
        Me.chkCaseSensitive.Size = New System.Drawing.Size(184, 24)
        Me.chkCaseSensitive.TabIndex = 6
        Me.chkCaseSensitive.Text = "Case Sensitive Search"
        '
        'cmbTableList
        '
        Me.cmbTableList.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList
        Me.cmbTableList.Location = New System.Drawing.Point(336, 80)
        Me.cmbTableList.Name = "cmbTableList"
        Me.cmbTableList.Size = New System.Drawing.Size(296, 26)
        Me.cmbTableList.TabIndex = 5
        '
        'txtSearchString
        '
        Me.txtSearchString.Location = New System.Drawing.Point(8, 152)
        Me.txtSearchString.Name = "txtSearchString"
        Me.txtSearchString.Size = New System.Drawing.Size(296, 24)
        Me.txtSearchString.TabIndex = 4
        Me.txtSearchString.Text = ""
        '
        'btnSpecifyFile
        '
        Me.btnSpecifyFile.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.btnSpecifyFile.Location = New System.Drawing.Point(272, 80)
        Me.btnSpecifyFile.Name = "btnSpecifyFile"
        Me.btnSpecifyFile.Size = New System.Drawing.Size(32, 40)
        Me.btnSpecifyFile.TabIndex = 3
        Me.btnSpecifyFile.Text = "..."
        '
        'lblAccessFileName
        '
        Me.lblAccessFileName.AllowDrop = True
        Me.lblAccessFileName.BackColor = System.Drawing.SystemColors.ActiveCaptionText
        Me.lblAccessFileName.BorderStyle = System.Windows.Forms.BorderStyle.Fixed3D
        Me.lblAccessFileName.Font = New System.Drawing.Font("Microsoft Sans Serif", 9.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblAccessFileName.Location = New System.Drawing.Point(8, 80)
        Me.lblAccessFileName.Name = "lblAccessFileName"
        Me.lblAccessFileName.Size = New System.Drawing.Size(264, 40)
        Me.lblAccessFileName.TabIndex = 2
        '
        'Label2
        '
        Me.Label2.Font = New System.Drawing.Font("Microsoft Sans Serif", 11.25!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label2.Location = New System.Drawing.Point(16, 56)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(176, 23)
        Me.Label2.TabIndex = 1
        Me.Label2.Text = "Access DB To Search:"
        '
        'Label1
        '
        Me.Label1.Font = New System.Drawing.Font("Microsoft Sans Serif", 15.75!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label1.Location = New System.Drawing.Point(8, 24)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(624, 23)
        Me.Label1.TabIndex = 0
        Me.Label1.Text = "Generic Access Database Search "
        Me.Label1.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
        '
        'lblTableToSearch
        '
        Me.lblTableToSearch.Font = New System.Drawing.Font("Microsoft Sans Serif", 11.25!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblTableToSearch.Location = New System.Drawing.Point(344, 56)
        Me.lblTableToSearch.Name = "lblTableToSearch"
        Me.lblTableToSearch.Size = New System.Drawing.Size(216, 23)
        Me.lblTableToSearch.TabIndex = 1
        Me.lblTableToSearch.Text = "Table To Search:"
        '
        'Label4
        '
        Me.Label4.Font = New System.Drawing.Font("Microsoft Sans Serif", 11.25!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label4.Location = New System.Drawing.Point(16, 128)
        Me.Label4.Name = "Label4"
        Me.Label4.Size = New System.Drawing.Size(176, 23)
        Me.Label4.TabIndex = 1
        Me.Label4.Text = "String To Find In Table:"
        '
        'MainMenu1
        '
        Me.MainMenu1.MenuItems.AddRange(New System.Windows.Forms.MenuItem() {Me.MenuItem1, Me.MenuItem3})
        '
        'MenuItem1
        '
        Me.MenuItem1.Index = 0
        Me.MenuItem1.MenuItems.AddRange(New System.Windows.Forms.MenuItem() {Me.mnuFileOpen, Me.mnuExit})
        Me.MenuItem1.Text = "&File"
        '
        'mnuFileOpen
        '
        Me.mnuFileOpen.Index = 0
        Me.mnuFileOpen.Text = "&Open File"
        '
        'mnuExit
        '
        Me.mnuExit.Index = 1
        Me.mnuExit.Text = "E&xit"
        '
        'MenuItem3
        '
        Me.MenuItem3.Index = 1
        Me.MenuItem3.MenuItems.AddRange(New System.Windows.Forms.MenuItem() {Me.mnuUsage, Me.mnuHistory, Me.mnuAbout})
        Me.MenuItem3.Text = "&Help"
        '
        'mnuUsage
        '
        Me.mnuUsage.Index = 0
        Me.mnuUsage.Text = "&Usage Instructions"
        '
        'mnuHistory
        '
        Me.mnuHistory.Index = 1
        Me.mnuHistory.Text = "&Version History"
        '
        'mnuAbout
        '
        Me.mnuAbout.Index = 2
        Me.mnuAbout.Text = "&About"
        '
        'DataGrid1
        '
        Me.DataGrid1.CaptionVisible = False
        Me.DataGrid1.DataMember = ""
        Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.DataGrid1.Location = New System.Drawing.Point(8, 184)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.Size = New System.Drawing.Size(640, 384)
        Me.DataGrid1.TabIndex = 1
        '
        'txtOutput
        '
        Me.txtOutput.Location = New System.Drawing.Point(56, 288)
        Me.txtOutput.Multiline = True
        Me.txtOutput.Name = "txtOutput"
        Me.txtOutput.ScrollBars = System.Windows.Forms.ScrollBars.Vertical
        Me.txtOutput.Size = New System.Drawing.Size(552, 248)
        Me.txtOutput.TabIndex = 2
        Me.txtOutput.Text = ""
        Me.txtOutput.Visible = False
        '
        'lblNotFound
        '
        Me.lblNotFound.Font = New System.Drawing.Font("Microsoft Sans Serif", 15.75!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblNotFound.Location = New System.Drawing.Point(64, 216)
        Me.lblNotFound.Name = "lblNotFound"
        Me.lblNotFound.Size = New System.Drawing.Size(536, 48)
        Me.lblNotFound.TabIndex = 3
        Me.lblNotFound.Text = "The search string was not found in the specified table"
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(656, 569)
        Me.Controls.Add(Me.lblNotFound)
        Me.Controls.Add(Me.txtOutput)
        Me.Controls.Add(Me.DataGrid1)
        Me.Controls.Add(Me.GroupBox1)
        Me.Icon = CType(resources.GetObject("$this.Icon"), System.Drawing.Icon)
        Me.Menu = Me.MainMenu1
        Me.MinimumSize = New System.Drawing.Size(664, 596)
        Me.Name = "Form1"
        Me.Text = "Generic Access Database Search"
        Me.GroupBox1.ResumeLayout(False)
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        lblNotFound.Hide()
        DataGrid1.Hide()
        lblStatus.Hide()
        'If we already have a filename from the registry we attempt to open it here
        strAccessFileName = GetSetting("SCIGenericAccessSearch", "Startup", "AccessFileName")
        lblAccessFileName.Text = strAccessFileName
        'If we have a real value we need to perform the associated processing
        If Not strAccessFileName = "" Then
            ConfigureConnection()
        End If
    End Sub

    Private Sub lblAccessFileName_DragDrop(ByVal sender As Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles lblAccessFileName.DragDrop
        If e.Data.GetDataPresent(DataFormats.FileDrop) Then
            Dim sFiles() As String = e.Data.GetData(DataFormats.FileDrop)
            If IsFileMDB(sFiles(0)) Then
                lblAccessFileName.Text = ""
                lblAccessFileName.Text = sFiles(0)
                strAccessFileName = sFiles(0)
                ClearForm()
                ConfigureConnection()
            End If
        End If
    End Sub

    Private Sub btnSpecifyFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSpecifyFile.Click
        LocateAccessFile()
    End Sub

    Private Sub LocateAccessFile()
        OpenFileDialog1.Filter = "Text files (*.mdb)|*.mdb"
        If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
            strAccessFileName = OpenFileDialog1.FileName
            lblAccessFileName.Text = ""
            lblAccessFileName.Text = strAccessFileName
            ClearForm()
            ConfigureConnection()
        End If
    End Sub

    Private Sub SaveFileToRegistry()
        SaveSetting("SCIGenericAccessSearch", "Startup", "AccessFileName", strAccessFileName)
    End Sub

    Private Sub lblAccessFileName_DragEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles lblAccessFileName.DragEnter
        e.Effect = DragDropEffects.All
    End Sub

    Private Sub ConfigureConnection()
        'We make quite a few assumptions here.  We are assuming that we need no userid or password to get in, etc.
        'This is just a generic search routine for now and can be modified in the future if needed for specialized use.
        ClearForm()
        cmbTableList.Items.Clear()
        blnNewSearchTable = True
        'This command sets the connection string but does not attempt to open the file, we do not know if the file is good
        conDBConnection.ConnectionString = _
                    "Provider=Microsoft.Jet.OleDB.4.0;" & _
                    "Data Source=" & strAccessFileName
        CreateTableList()
    End Sub

    Function IsFileMDB(ByVal Filename As String) As Boolean
        'This function will examine a filename dropped onto the form and will return true if is is a 
        'filename with a .MDB extension.  Here are the steps for this operation:
        '
        '  1.  Get the actual filename after the last "\" character
        '  2.  Verify that there is a period in the filename denoting an extension - if not, display message and return false
        '  3.  Verify that the extension is MDB or txt - if not, display message and return false
        '  4.  If you have gotten to the end, return true
        Dim intCharacterCounter, intTotalLength As Integer
        'Get the total length of the string
        intTotalLength = Len(Filename)
        'Find the last "\" character in the string
        intCharacterCounter = InStrRev(Filename, "\")
        If intCharacterCounter = 0 Then
            MsgBox("The filename appears to be totally invalid, never found a '\' character in the string!")
            Return False
        End If
        'change the string so it only has the filename after the last '\'
        Filename = Microsoft.VisualBasic.Right(Filename, intTotalLength - intCharacterCounter)
        'find the last period in the filename
        intCharacterCounter = InStrRev(Filename, ".")
        If intCharacterCounter = 0 Then
            MsgBox("The filename does not appear to have an extension, we need a MDB extension to process it.")
            Return False
        End If
        'Get the length of the string now
        intTotalLength = Len(Filename)
        'change the string so we only have the extension after the '.'
        Filename = Microsoft.VisualBasic.Right(Filename, intTotalLength - intCharacterCounter)
        'take out leading and trailing spaces
        Filename = Trim(Filename)
        'verify that it is a three-character extension
        If Not Len(Filename) = 3 Then
            MsgBox("The filename does not appear to have a three-character extension, we need a MDB extension to process it.")
            Return False
        End If
        'check extension to see if it is txt
        If UCase(Filename) = "MDB" Then
            Return True
        Else
            MsgBox("We can only process the file if it has an extension of MDB.")
            Return False
        End If
    End Function

    Private Sub ClearForm()
        'Here we need to clear out the dataset we were using, the datagrid on the form, and hide the datagrid and label
        lblNotFound.Hide()
        DataGrid1.Hide()
        dtDisplayContents.Rows.Clear()
    End Sub

    'When a new search is initiated:
    'hide both output items
    'clear the output data table

    'When a new access table is specified:
    'hide both output items
    'clear the output data table
    'flag the search to clear and re-populate the search table

    'When a new database is specified:
    'Get a new list of tables
    'hide both output items
    'clear the output data table
    'flag the search to clear and re-populate the search table

    Private Sub CreateTableList()
        '
        'This is a procedure from the Internet explaining how to retrieve the list of tables from the 
        'database - it needs some work 
        Dim intRowCounter, intDataTableCount As Integer
        Dim DT As DataTable
        Dim SchemaGUID As OleDb.OleDbSchemaGuid
        Dim MyRow As DataRow
        Dim MyCol As DataColumn

        'Open the connection to the database
        Try
            conDBConnection.Open()
        Catch ex As Exception
            MsgBox("Error occurred opening the database to read the table list, error is: " & vbNewLine & _
                    ex.Message)
            blnAccessFileOpened = False
            Exit Sub
        End Try

        'Retrieve the schema of the database into the datatable
        Try
            DT = conDBConnection.GetOleDbSchemaTable(SchemaGUID.Tables, Nothing)
        Catch ex As Exception
            MsgBox("Error occured getting the list of tables from the database, error is:" & vbNewLine & _
                ex.message)
            blnAccessFileOpened = False
            Exit Sub
        End Try
        '
        ' This is a routine from the web that can be used to show every type of entity in an Access database.  The routine is
        ' being preservered here for future reference.  The output from this routine is listed farther down in this section.
        '

        ' txtOutput.Text = ""
        ' MsgBox("We got here")


        '  For Each MyCol In DT.Columns
        '  If Not DT.Rows(0).IsNull(MyCol.ColumnName) Then
        '  txtOutput.Text += MyCol.ColumnName & "  :  "
        '  End If
        '  Next
        '  txtOutput.Text += vbCrLf
        '  For Each MyRow In DT.Rows
        '  For Each MyCol In DT.Columns
        '  If Not MyRow.IsNull(MyCol.ColumnName) Then
        '  txtOutput.Text += MyRow(MyCol.ColumnName) & "  :  "
        '  End If
        '  Next
        '  txtOutput.Text += vbCrLf
        '  Next

        '  txtOutput.Text += vbCrLf & vbCrLf & "Phil output follows, here are the data tables:"

        If DT.Rows.Count = 0 Then
            MsgBox("This is a totally empty table!")
            conDBConnection.Close()
            Exit Sub
        End If

        For intRowCounter = 0 To DT.Rows.Count - 1
            If DT.Rows(intRowCounter).Item("TABLE_TYPE") = "TABLE" Then
                cmbTableList.Items.Add(DT.Rows(intRowCounter).Item("TABLE_NAME"))
                intDataTableCount += 1
            End If
        Next

        If intDataTableCount = 0 Then
            MsgBox("There were no data tables found in the search table!")
        Else
            cmbTableList.SelectedIndex = 0
        End If

        conDBConnection.Close()
        'It might seem funny to close the connection and set the file opened flag to true - however, this means we have opened the file
        'successfully and have verified we can perform searches on the file.
        blnAccessFileOpened = True
        '
        'Here is a sample output showing the data table:
        '
        'TABLE_NAME: TABLE_TYPE() : DATE_CREATED() : DATE_MODIFIED()
        'Customer  :  TABLE  :  3/5/2001 12:35:14 PM  :  3/3/2004 11:57:20 AM  :  
        'MSysAccessObjects  :  ACCESS TABLE  :  3/3/2004 4:59:22 PM  :  3/3/2004 4:59:22 PM  :  
        'MSysACEs  :  SYSTEM TABLE  :  3/5/2001 12:33:40 PM  :  3/5/2001 12:33:40 PM  :  
        'MSysIMEXColumns  :  ACCESS TABLE  :  3/5/2001 12:36:58 PM  :  3/5/2001 12:36:58 PM  :  
        'MSysIMEXSpecs  :  ACCESS TABLE  :  3/5/2001 12:36:58 PM  :  3/5/2001 12:36:58 PM  :  
        'MSysObjects  :  SYSTEM TABLE  :  3/5/2001 12:33:40 PM  :  3/5/2001 12:33:40 PM  :  
        'MSysQueries  :  SYSTEM TABLE  :  3/5/2001 12:33:40 PM  :  3/5/2001 12:33:40 PM  :  
        'MSysRelationships  :  SYSTEM TABLE  :  3/5/2001 12:33:40 PM  :  3/5/2001 12:33:40 PM  :  
        'Order  :  TABLE  :  3/5/2001 12:42:15 PM  :  3/5/2001 1:51:20 PM  :  
        'OrderDetail  :  TABLE  :  3/5/2001 1:56:08 PM  :  3/5/2001 1:59:16 PM  :  
        'qryCustOrderDate  :  VIEW  :  3/5/2001 2:01:34 PM  :  3/5/2001 2:01:34 PM  :  
        'Salesman  :  TABLE  :  3/5/2001 1:52:44 PM  :  3/5/2001 1:54:28 PM  :  
        '
    End Sub

    Private Sub PerformSearch()
        '
        'Some design thoughts -
        'When a new database file is chosen we want to clear the entire form, however
        'when a new search term is entered we want to keep the same table and keep the same dataset from
        'the table and just do a new text search
        'It would make sense to grab the entire table from the database in a dataset, make a blank
        'copy of the dataset, and then as we find matches on any column move the entire row to the display
        'dataset that will be bound to the datagrid only if we have rows to display - otherwise we
        'display our "no match was found label"
        '
        Dim strTableToSearch As String
        Dim intRowCounter, intColumnCounter, intNumberOfMatches As Integer
        Dim strStringToFind, strStringFromTable As String
        Dim blnUpshift, blnRowCopied As Boolean
        Dim drNewRow As DataRow
        Dim arlMaximumFieldLengths As New ArrayList

        'Checking to see if we have the correct data to proceed
        If lblAccessFileName.Text = "" Then
            MsgBox("You must specify an Access database before attempting to search!")
            Exit Sub
        End If
        If blnAccessFileOpened = False Then
            MsgBox("The Access file was not successfully opened, we cannot perform a search")
            Exit Sub
        End If
        If cmbTableList.Items.Count = 0 Then
            MsgBox("You must pick a database with some data tables to search!")
            Exit Sub
        End If
        If txtSearchString.Text = "" Then
            MsgBox("You must enter a search string before attempting a search!")
            Exit Sub
        End If


        'Reset form for new search
        ClearForm()

        'Checking to see if we are doing a case sensitive search
        If chkCaseSensitive.Checked Then
            blnUpshift = False
        Else : blnUpshift = True
        End If

        If blnNewSearchTable Then 'if we are using the same table, let's not get a new copy
            'Getting a copy of the table we will use for our search
            lblStatus.Text = "Reading table from database...."
            lblStatus.Show()
            Me.Refresh()
            dtAllContents.Rows.Clear() 'clear out the table from the previous search
            dtAllContents.Columns.Clear() 'we also need to clear all columns to destroy schema
            strTableToSearch = cmbTableList.SelectedItem 'Setting the table we will search
            odbcmdSelect.CommandText = "SELECT * FROM [" & strTableToSearch & "]"
            odbcmdSelect.Connection = conDBConnection
            odbaAdapter.SelectCommand = odbcmdSelect
            Try
                odbaAdapter.Fill(dtAllContents) 'We fill the "all contents" datatable with a copy of the table so we can search in memory
            Catch ex As Exception
                MsgBox("Error attempting to read the table into the 'all contents' dataset, error is: " & vbNewLine & _
                    ex.Message)
                lblStatus.Hide()
                Exit Sub
            End Try
        End If
        lblStatus.Hide()

        blnNewSearchTable = False 'We turn it off here, it will get turned on again if the table changes

        dtDisplayContents = dtAllContents.Clone 'We set the schema of the "display contents" datatable where we will write the results

        'Now we have all the rows in the search table.  Time to find our string in there
        strStringToFind = txtSearchString.Text
        If blnUpshift Then strStringToFind = UCase(strStringToFind) 'If we don't care about case, upshift the string to find
        lblStatus.Text = "Searching for string..."
        lblStatus.Show()
        Me.Refresh()
        'Do we need to update a status label on the form to "please wait"?
        For intRowCounter = 0 To dtAllContents.Rows.Count - 1
            blnRowCopied = False 'since we are starting a new row, set it to not copied
            For intColumnCounter = 0 To dtAllContents.Columns.Count - 1
                If dtAllContents.Rows(intRowCounter).Item(intColumnCounter).GetType.ToString = "System.DBNull" Then
                    strStringFromTable = "" 'if the item contains a null we change it to an empty string, we cannot convert from null
                Else
                    strStringFromTable = CStr(dtAllContents.Rows(intRowCounter).Item(intColumnCounter))
                End If
                If blnUpshift Then strStringFromTable = UCase(strStringFromTable) 'if not doing case sensitive, upshift
                Trim(strStringFromTable)
                If Not InStr(strStringFromTable, strStringToFind) = 0 Then 'if the result is not zero then we found it
                    intNumberOfMatches += 1
                    If Not blnRowCopied Then 'we only copy this row to the output if this row was not previously copied
                        drNewRow = dtAllContents.NewRow 'create the new row with the same schema
                        drNewRow = dtAllContents.Rows(intRowCounter) 'fill the new row with the data
                        dtDisplayContents.ImportRow(drNewRow) 'import the new row into the display contents datatable                        
                    End If
                    blnRowCopied = True 'set this row as copied so it won't be copied again
                End If
            Next
        Next
        lblStatus.Hide()

        'Here is where we display the results of our efforts

        If intNumberOfMatches > 3 Then
            DataGrid1.AlternatingBackColor = Color.WhiteSmoke
        Else
            DataGrid1.AlternatingBackColor = Color.White
        End If


        If intNumberOfMatches > 0 Then 'only display datagrid if we have contents
            DataGrid1.DataSource = dtDisplayContents
            Dim AddTableStyles As New ConfigureTableStyle
            AddTableStyles.Datatable = dtDisplayContents.Copy
            AddTableStyles.Datagrid = DataGrid1
            AddTableStyles.FillTableStyle()
            If AddTableStyles.ErrorOccured Then
                MsgBox("Error occurred while attempting to configure datagrid tablestyles, error was: " & vbNewLine & _
                    AddTableStyles.ErrorMessage)
                Exit Sub
            Else
                DataGrid1.TableStyles.Add(AddTableStyles.tablestyle)
                DataGrid1.Show()
            End If
        Else
            lblNotFound.Show()
        End If
    End Sub

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        PerformSearch()
    End Sub

    Private Sub cmbTableList_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbTableList.SelectedIndexChanged
        'When the table name changes we set a flag and clear the form.  When the search is actually performed, the new table name will 
        'be read from the combobox
        blnNewSearchTable = True
        ClearForm()
    End Sub

    Private Sub txtSearchString_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtSearchString.KeyPress
        'If the enter key is pressed in the search field we assume that is the signal to perform the search
        If e.KeyChar = vbCr Then PerformSearch()
    End Sub

    Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        'As we are closing we want to save the filename to the registry
        SaveFileToRegistry()
    End Sub

    Private Sub Form1_Resize(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Resize
        DataGrid1.Height = Me.Height - GroupBox1.Height - 45
        DataGrid1.Width = Me.Width - 15
    End Sub

    Private Sub mnuFileOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuFileOpen.Click
        LocateAccessFile()
    End Sub

    Private Sub mnuExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuExit.Click
        Me.Close()
    End Sub

    Private Sub mnuUsage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuUsage.Click
        MsgBox("This Generic Access Search utility will allow you to search any column in any table in any Access file" & vbNewLine & _
                "for any string and will display all rows containing that string." & vbNewLine & _
                vbNewLine & _
                "The first step is to specify the Access file you wish to search, either by clicking on the File-->Open" & vbNewLine & _
                "command in the menu bar or clicking on the elipsis (...) next to the filename label on the form or" & vbNewLine & _
                "simply by dragging and dropping the file onto the label from Windows Explorer or a similar window." & vbNewLine & _
                vbNewLine & _
                "When the Access file is opened if there is more than one table contained in the database, a pull-down" & vbNewLine & _
                "menu on the right side of the form will allow you to choose which table you wish to search." & vbNewLine & _
                vbNewLine & _
                "By default the program will perform a case-insensitive seach on the string.  In other words," & vbNewLine & _
                "if you type in 'hard drive' as the search string it will return rows that contain strings such" & vbNewLine & _
                "as 'Hard Drive' and 'HARD DRIVE' and 'hArD dRiVe'.  If you wish to make the search case sensitve" & vbNewLine & _
                "there is a checkbox to enable this feature" & vbNewLine & _
                vbNewLine & _
                "Also please note that this is a simple search string - there is no way to specify wildcards or" & vbNewLine & _
                "specify a boolean search such as 'show all rows containing 'hard drive' without 'maxtor' in the line." & vbNewLine & _
                vbNewLine & _
                "Then you either type the search string into the textbox or copy it from the clipboard and either" & vbNewLine & _
                "hit enter or click the search button." & vbNewLine & _
                vbNewLine & _
                "At this point, if the search string was located in any rows in the table those rows will be displayed" & vbNewLine & _
                "in the datagrid with no indication of where in the row the string was located.  If the string was not" & vbNewLine & _
                "found in any row in the table, a message will appear signifying this condition." & vbNewLine & _
                vbNewLine & _
                "When the program is closed the location of the Access file will be remembered and the next time" & vbNewLine & _
                "the program is started it will automatically open the file assuming this is the file you wish" & vbNewLine & _
                "again.  If this is not the case, the new search database can be specified for your new search." & vbNewLine & _
                vbNewLine & _
                "In order to improve the execution speed of the searches, the table is read into memory and the" & vbNewLine & _
                "search is performed there.  Also, if you have several searches to perform on one table, the" & vbNewLine & _
                "table is held in memory until you switch to a new table or database.")

    End Sub

    Private Sub mnuHistory_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuHistory.Click
        MsgBox("Version 1.0.0      10-29-2005    Initial Creation")
    End Sub

    Private Sub mnuAbout_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuAbout.Click
        MsgBox("Generic Access Search" & vbNewLine & _
                "Created by Phil Persson for SCI" & vbNewLine & _
                "Version 1.0.0")
    End Sub


End Class