Horizontal Scrolling for Code

Horizontal scrolling can be obtained by using a tag as follows using style="overflow: auto;"

        Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\CIM_CourseDevelopment\BillOfMaterial.accdb; Persist Security Info=False"

When the line is shorter, the scroll bar disappears

        Dim connStr As String = "Hello"

Obtaining the MS Access Connection String for Visual Studio

Introduction

This walk thru will show you how to obtain the connection string for connecting to your MS Access Database.

Procedure

1.    Create a Windows Form App.

2.    Click on “Data Sources” on the left side bar and

3.    Click “Add New Data Source”

4.    Select “Database” and click [Next]

5.    The "Choose a Database Model" dialog box is displayed. Dataset is the only option. Click [Next].

6.    Click on [New Connection]. The "Add Connection" dialog is displayed. Note that the data source needs to be “Microsoft Access Database File (OLE DB)”. If it isn’t, click the [Change] button to select the correct type of data source.

7.    [Browse] to select your MS Access database file.

8.    [Test Connection] will tell you if the connection was successful. Do not change the user name or password.

9.    Click [Ok] to exit the dialog.

10. In the Data Source Configuration Wizard dialog, click on “Show the connection string that you will save in the application”  You should see a connection string similar to the following,

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\CIM.accdb

The string indicates the provider is Microsoft.ACE.OLEDB version 12.0, and the path to the database file.

Copy the connection string to the clip board (select the entire string, the hit Ctrl-C) and paste it into your code to be used when connecting to your MS Access Database.

Inverting the Colours in Windows 10

At the search bar, type: Adjust color filters

Then turn on Color filters

Choose Inverted.

This will give you white text on a black background in most environments including Word, Chrome, Visual Studio, SolidWorks, and Revit.

Inserting New Record into MS Access Database using VB.Net, concatenating a string variable into the SQL Command

The following is an example of inserting a record into the ItemMaster table where field 'Description' will have a value of 'Cardinal' put in it.

Note that a String variable is created with the value of Cardinal and the SQL String is parced together with this information. Note the component of the SQL string: '" + strDesc + "'  This is where the string variable strDesc is concatenated into the string. The final SQL string looks like:

INSERT INTO ItemMaster (Description) VALUES ('Cardinal')

Imports System.Data.OleDb.OleDbConnection
Module Module1
    Sub Main()
        Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\CIM_CourseDevelopment\BillOfMaterial.accdb; Persist Security Info=False"
        Dim cnn As New OleDb.OleDbConnection(connStr)
        Try
            cnn.Open()
            Dim strDesc As String = "Cardinal"
            Dim command As New OleDb.OleDbCommand("INSERT INTO ItemMaster (Description) VALUES ('" + strDesc + "')", cnn)
            command.ExecuteNonQuery()
            cnn.Close()
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Sub
End Module

SQL Select Statement: MS Access Database from VB.net

The following is a simple example of using the SELECT statement. The ItemMaster table which it is operating on has the following fields:

Field	Type
PartNumber	AUTOINCREMENT PRIMARY KEY
Description	varchar(255)

I am selecting all fields from the table (SELECT * ...).

• The first field I retrieve will be of type AUTOINCREMENT PRIMARY KEY which is stored as a Long in MS Access so I use reader.GetInt32(0).ToString() to get the first field, at location 0 using the GetInt32. I need to convert it to a string to display it.
• The second field I retrieve will be of type varchar(255) which is already a string so I can directly use the reader function  reader(1)  to get the second field.

Imports System.Data.OleDb.OleDbConnection
Module Module1
    Sub Main()
        Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\CIM_CourseDevelopment\BillOfMaterial.accdb; Persist Security Info=False"
        Dim cnn As New OleDb.OleDbConnection(connStr)
        Try
            cnn.Open()
            Console.WriteLine("Connection Open!")
            Dim command As New OleDb.OleDbCommand("SELECT * FROM ItemMaster", cnn)
            Dim reader As OleDb.OleDbDataReader = command.ExecuteReader()
            While reader.Read()
                Console.WriteLine("PartNumber: " + reader.GetInt32(0).ToString() + vbTab + "Description " + reader(1))
            End While
            cnn.Close()
        Catch ex As Exception
            Console.WriteLine("Can not open connection " + ex.Message)
        End Try
        Console.ReadLine()
    End Sub
End Module

The following is an example of using the MAX statement to find the maximum part number. A separate function has been created to execute the MAX statement. Note that the reader.Read() does not have to be part of While loop if there is only one value that you anticipate to read. The MaxPartNumber function uses the GetInt32 function and the (0) refers to the first field that is returned.

Imports System.Data.OleDb.OleDbConnection
Module Module1
    Sub Main()
        Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\CIM_CourseDevelopment\BillOfMaterial.accdb; Persist Security Info=False"
        Dim cnn As New OleDb.OleDbConnection(connStr)
        Try
            cnn.Open()
            Console.WriteLine("The max part number is: " + MaxPartNumber(cnn).ToString)
            cnn.Close()
        Catch ex As Exception
            Console.WriteLine("Can not open connection " + ex.Message)
        End Try
        Console.ReadLine()
    End Sub
 
    Function MaxPartNumber(cnnMax As OleDb.OleDbConnection) As Long
        Dim command As New OleDb.OleDbCommand("SELECT MAX(PartNumber) FROM ItemMaster", cnnMax)
        Dim reader As OleDb.OleDbDataReader = command.ExecuteReader()
        reader.Read()
        Return reader.GetInt32(0)
    End Function
End Module

Adding custom CSS to Blogger Post

Publish the post, then go to Theme > Customize > Advanced > Add CSS > then paste your custom table css here. To make the tables look like you see below, I added the following CSS:

table, th, td {
  border: 1px solid black;
  border-collapse: collapse;
}
th, td {
  padding-left: 5px;
  padding-right: 5px;
  text-align: left;
}

Name:	Bill Gates
Telephone:	55577854
	55577855

Thanks to the help from https://support.google.com/blogger/thread/29091352?hl=en, 

Using SQL command to create a table in database using VB.Net Framework

Note that this required a .Net Framework Application. When Microsoft brought out .Net Core, they stop supporting OleDb

Module Module1
    Sub Main()
        Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\CIM_CourseDevelopment\BillOfMaterial.accdb; Persist Security Info=False"
        Dim cnn As New OleDb.OleDbConnection(connStr)
        Try
            cnn.Open()
            Dim command As New OleDb.OleDbCommand("CREATE TABLE MyTable (ID Integer, Data varchar(50))", cnn)
            command.ExecuteNonQuery()
            cnn.Close()
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Sub
End Module

Connect to MS Access From .Net FrameWork Application

Note that this required a .Net Framework Application. When Microsoft brought out .Net Core, they stop supporting OleDb

Imports System.Data.OleDb.OleDbConnection
Module Module1
    Sub Main()
        Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\CIM_CourseDevelopment\BillOfMaterial.accdb; Persist Security Info=False"
        Dim cnn As New OleDb.OleDbConnection(connStr)
        Try
            cnn.Open()
            Console.WriteLine("Connection established")
            Console.ReadLine()
            cnn.Close()
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Sub
End Module

SQL Stored Procedure SELECT Statement: MS Access Database from VB.net

Imports System.Data.OleDb.OleDbConnection
Module Module1
    Sub Main()
        Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\CIM_CourseDevelopment\BillOfMaterial.accdb; Persist Security Info=False"
        Dim cnn As New OleDb.OleDbConnection(connStr)
        Try
            cnn.Open()
            Console.WriteLine("Connection Open!")
            Dim command As New OleDb.OleDbCommand("SELECT * FROM BOM WHERE Parent_PartNumber = @ParentID", cnn)
            command.Parameters.AddWithValue("@ParentID", 2)
            'command.Parameters.AddWithValue("@ParentID", OleDb.OleDbType.Integer).Value = 2
            Dim reader As OleDb.OleDbDataReader = command.ExecuteReader()
            While reader.Read()
                Console.WriteLine(reader.GetInt32(0).ToString() + vbTab + reader.GetInt32(1).ToString() + vbTab + reader.GetInt32(2).ToString())
            End While
            cnn.Close()
        Catch ex As Exception
            Console.WriteLine("Can not open connection " + ex.Message)
        End Try
        Console.ReadLine()
    End Sub
End Module

Note that some sites recommend using the:

            'command.Parameters.AddWithValue("@ParentID", OleDb.OleDbType.Integer).Value = 2

I found that is worked fine with just using:

            command.Parameters.AddWithValue("@ParentID", 2)