Bookmark and Share




Visual Basic 2010 / SQL - Introduction (2020)
visual basic 2010
Introduction




This tutorial largely composed of two parts: Visual Basic, SQL database and MS Office Access

Visual Basic

Visual Basic offers the easiest way to write programs for Windows. It works with MS Office and on the Internet.

  • Visual Basic (VB) - Simple and quick to begin creating standalone Windows application.
  • Visual Basic for Applications(VBA) - An implementation of VBA is built into all MS Office applications. It shares the same core VB language but has different available objects in each application. Word has an ActiveDocument object and Excel has an ActiveSheet object. It runs within a host rather than as a standalone application with VBA because it has not native compiler. But we can create a script, hidden within the document file, and so on.
  • Visual Basic Script(VBScript) - This is a derivative of Visual Basic. It is the default language for Active Server Pages (ASP). It can be used in Windows scripting and client-side web page scripting for IE. Although it resembles VB in syntax, it is a separate language and it is executed by vbscript.dll as opposed to the VB runtime. ASP and VBScript should not be confused with ASP.NET which uses the .NET Framework for compiled web pages. VBScript is a scripting language and it shares the same core VB language. Scripts written in VBScript are interpreted by a script engine that processes the instructions to execute the script. The script engine can be invoked either from within the Windows GUI or at a Command Prompt, or IE.

Database

There are tools available to help us create and manage databases. Many of them hide the complexities of the underlying data structures. For instance, Ruby on Rails abstracts all database access and makes most direct access unnecessary, as do component technologies such as Enterprise JavaBeans and lots of object-oriented frameworks. For windows, Access does it. However, we still need to understand how relational databases work.

Relational databases store data in tables, which consist of rows and columns. Each table has at least one column, and each column has a type associated with it, which limits the type of data that can be stored in the column. Most tables have keys. One of the keys is designated to be the primary key. A table can be linked to another table using a foreign key.

  • Structured Query Language (SQL)
    SQL provides mechanisms for most kinds of database manipulations. The SQL Server DataBase Management System (DBMS) that is bundled with Visual Basic takes the Relational DataBase Management Systems (RDBMS) like Oracle or IBM DB2.
  • Data normalization
    Normalization rules insist that data is organize efficiently, and without duplication or redundancy, in order to reduce the potential for anomaly during data operations. They require each table to have a primary key column and permissible data types must be defined for all other columns. This determines whether cells in the column may contain text or numbers, and whether cells may be left empty or not.
  • Data (referential) integrity
    Another aspect of RDBMS, it requires that records referred in another table cannot be deleted unless the reference is the first deleted. In other words, we can't delete a row from a table if the foreign key of another table references the row.

MS Office Access

Microsoft Office Access 2010 is a RDBMS that combines the relational Microsoft Jet Database Engine with a GUI and software-development tools.

Access stores data in its own format based on the Access Jet Database Engine. It can also import or link directly to data stored in other applications and databases.

We can use Access to develop application software, or to build simple applications. Access is supported by Visual Basic for Applications, an object-oriented programming language that can reference a variety of objects including DAO (Data Access Objects), ActiveX Data Objects, and many other ActiveX components. Visual objects used in forms and reports expose their methods and properties in the VBA programming environment, and VBA code modules may declare and call Windows operating-system functions.

Access is compatible with SQL (structured query language), queries can be viewed graphically or edited as SQL statements, and SQL statements can be used directly in Macros and VBA Modules to manipulate Access tables. Users can mix and use both VBA and "Macros" for programming forms and logic and offers object-oriented possibilities. VBA can also be included in queries.

Microsoft Access is a file server-based database. Unlike client'server relational database management systems (RDBMS), Microsoft Access does not implement database triggers, stored procedures, or transaction logging. Access 2010 includes table-level triggers and stored procedures built into the ACE data engine. Thus a Client-server database system is not a requirement for using stored procedures or table triggers with Access 2010. Tables, queries, Forms, reports and Macros can now be developed specifically for web base application in Access 2010. Integration with Microsoft SharePoint 2010 is also highly improved.





Taste of Visual Basic

All examples throughout this tutorial will be created for Visual Basic 10.0

Let's open the New Project Dialog and select the Windows Forms Application template icon.


SelectingWindowsForms

Enter a project name and then click OK.


FormDesigner

Now a new tabbed Form Designer window appears with a default Form displayed and a properties window is added below the Solution Explorer. This Form Designer is where we create visual interfaces for our applications and the Properties window contains details of the item which is selected in the Form Designer window.

Now, we are ready to make a Windows application since Visual Basic IDE has all the resources to build it. All we have to do is to launch it. Debug => Start Without Debugging from the top menu.


SimplestApplication

It was the one of the simplest application written in Visual Basic.

The following Toolbox contains a lot of visual controls. They are the building blocks of our applications.


Toolbox ButtonSelection

From the Common Controls, drag (double click) the Button item. Then, the Button control appears in the Form Designer. We can run the application.


ButtonRun

Behind the scene, IDE generates a code for us to incorporate the visual controls we add to our program interface. If we double-click on the Button control we have added to the default Form. A new tabbed code editor window opens.


ButtonCode

So, we can add the instruction for the case when the button pressed. Let's type in:

MsgBox("My first button message!")

ButtonMessage

If we run it and click the button, we get the message window.


ButtonMessageRun



Button, Label, and TextBox Controls

Now, it's time to make more complicated window. Using Button, Label, and TextBox from Toolbox, make something like this:


PreMultiplyForm

Then modify the Text and (Name) Properties.


MultiplyProperties

To get the proper response for the button click, let's put the following into the code:


MultiplyCode

Run the application. Type in the number into the two TextBox, and press Multiply button.


MultiplyResultForm




InputBox Dialog

In this section, the result from the user input is used for title of the window when user presses the button.


InputBoxA

The code we need to handle the button press event is:

Me.Text = InputBox("Enter a Window Title")

Note that we used the Me keyword to address the Form.


InputBoxAResult

By adding a third string within the parentheses, we can specify a default response that will appear in the text field when the InputBox is called:

Me.Text = InputBox("Enter a Window Title",
                   "Title Selector", "Beautiful Window")


InputBoxAB