Introduction to Databases

A database can also be stored in one computer but accessed by different computers in a network. Another database can be created and stored in a server to be accessed through the Internet. These and other related scenarios should be dealt with to create and distribute an effective database.

You may plan to create a database that would be used by one person using one computer. As your job becomes more effective, you could be asked to create another database that would be accessed by different people. Regardless of why and how, after creating a database, you should have a way of making it available to those who would use. To do this, you must create a data source. With Microsoft Access on Microsoft Windows, you have two main options.

In Microsoft Access, a database is created as a computer file and it has a path, that is, where the database file is located. The path to a file is also known as its location. The path to a database, including its name, is also called the data source. In some of your database operations, you might be asked to provide a data source for your database. In this case, provide the complete path followed by the name of the database.

Using the path to a database is probably the easiest way to access it but this is more convenient if you are working in Microsoft Access. If you plan to access your database from another programming environment, then you should create an ODBC data source. To do this, in the Control Panel or the Administrative Tools, double-click Data Source (ODBC) to open the ODBC Data Source Administrator:

To proceed, click the Microsoft Access and click the Add button. This would launch a wizard. In the first page of the Create New Data Source wizard, if the database was created with a version of Microsoft Access prior to 2007, click Microsoft Access Driver (*.mdb). If the database was created with Microsoft Office Access 2007, click Microsoft Access Driver (*.mdp, *.accdb):

Click Finish. In the following screen, you would be asked to enter a name for the data source. You can enter the name in one or more words. The name would be used by the applications that need to access the database. This means that you should pay attention to the name you give. In the Description text box, you can enter a short sentence anyway you like. To specify the database that would be used, click Select and select an mdb database:

After selecting the necessary database, if you need to be authenticated in order to use the database (if the database is protected), click the Advanced button:

By default, a database is meant to allow anybody to use. In this case, you can leave the Login Name and the Password empty. Otherwise, type the necessary credentials. After using the Set Advanced Options dialog box, click OK (or Cancel to keep it the way it previously was).

After entering the necessary information and selecting the desired database, you can click OK twice.

In the previous lesson, we had an introduction to the various libraries used in Microsoft Access and we saw how to make them available to a database. In the Microsoft Access Object Library, a database is an object of type Database. Therefore, if you want to start a variable associated with a database, you can declare a variable of type Object or of type Database. To assist you with creating a database, the Workspace object is equipped with a method named CreateDatabase. Its syntax is:

Workspace.CreateDatabase(ByVal Name As String, _
			 ByVal locale As String, _
			 ByVal options As Variant) As Database

The first argument can be the name of the database you want to create. If you provide only the name of the database, it would be created in the same folder where the action was initiated. For example, if you are already working in a database and you create a new database by providing a name, the new database would be created in the same folder where the current database resides. Here is an example:

Private Sub cmdDatabase_Click()
    DBEngine.Workspaces(0).CreateDatabase("Exercise.accdb", . . .)
End Sub

If you want, you can store the new database in a drive and folder of your choice. To do this, provide the complete path and the name of the database as the first argument. This would be done as follows:

Private Sub cmdCreate_Click()
    DBEngine.Workspaces(0).CreateDatabase("C:\Microsoft Access " & _
					  "Database Development\Exercise.accdb", . . .)
End Sub

The second argument to this method is required and it specifies the language used for the database. This is also referred to as the collation. This argument is passed as a constant named value and can be one of the following:

 Here is an example:

Private Sub cmdCreate_Click()
    CreateDatabase("Exercise.accdb", dbLangGreek)
End Sub

The third argument of the CreateDatabase() method is used to pass some options to use when creating the database. This optional argument can be a constant specified as dbEncrypt, if you want the database to be encrypted. If you don't pass this constant, the database would not be encrypted.

If you want to specify the version of Microsoft Jet used in the new database, the options argument can be one of the following values:  dbVersion10, dbVersion11, dbVersion20, or dbVersion30. If you don't specify one of these values, the dbVersion30 would be used.

If you want the database to be encrypted and you want to specify the version of Microsoft Jet used in the new database, combine the dbEncrypt constant with one of the version values.

When the CreateDatabase() method has finished, it returns a reference to the database that was created. In fact, you must always obtain this reference. The database is recognized as the Database object of the workspace. To get it, first declare a variable of type Database or Object. Here is an example:

Private Sub cmdDatabase_Click()
    Dim dbExercise As Database
    
End Sub

To get a reference to the new database, use the Set operator and assign the returned value of the method to your Database variable. Here is an example that creates a new database named Exercise.accdb in the current folder:

Private Sub cmdDatabase_Click()
    Dim dbExercise As Database
    
    Set dbExercise = DBEngine.Workspaces(0).CreateDatabase( _
			"Exercise.accdb", dbLangGeneral)
End Sub

We mentioned that, when you start a database, the DBEngine object is automatically made available to you and it is equipped with a default workspace whose index is 0. Because these are already available, you do not have to include them in your statements if you want to use only the defaults. Therefore, you can omit them when creating a database. The above code could be written as:

Private Sub cmdDatabase_Click()
    Dim dbExercise As Database
    
    Set dbExercise = CreateDatabase("Exercise.accdb", dbLangGeneral)
End Sub

And it would produce the same effect.

Once a database exists, you can open it to use it when necessary. To support the ability to open a database, the Workspace class is equipped with a method named

Public Function Workspace.OpenDatabase(ByVal Name As String, _
				       ByVal Options As Boolean, _
				       ByVal ReadOnly As Boolean, _
				       ByVal Connect As String) As Database

Only the first argument is required and it is passed as string. You can pass the name of the database file with its extension. Here is an example:

Private Sub cmdOpenDatabase_Click()
    DBEngine.Workspaces(0).OpenDatabase "Example.accdb"
End Sub

After opening the database, if you want to work on it, you should get a reference to it when opening it. To allow you to do this, the OpenDatabase() method returns a Database object. Here is an example:

Private Sub cmdOpenDatabase_Click()
    Dim dbExample As Database
    
    Rem Open the Example.accdb database and get a reference to it
    Set dbExample = DBEngine.Workspaces(0).OpenDatabase("Example.accdb")
    
    . . . Use the dbExample database as you see fit

End Sub

When opening the database, you can lock it so that other people or applications cannot access it at the same time with you. To prevent other items (they are called processes) from accessing the database, pass a second argument as True. On the other hand, you may want to allow other people or applications to be able to access the same database. To specify this option, pass the second argument as False.

Like the second argument, the third is optional. If you are opening the database and want to do something on it, such as modifying it, pass the third argument as False. If you don't want to perform any modification action on the database, pass the third argument as True.

The fourth argument allows you to provide connection information.

While a database is being used, it consumes computer resources such as memory. After using it, you should close the database to free the resources it was using and make them available to other applications.

To provide the ability to close a database, the Database class is equipped with a method named Close. Its syntax is:

Database.Close()

As you can see, this method does not take any argument. Before calling it, make sure you get a reference to the database to be closed. Here is an example:

Private Sub cmdCreateDatabase_Click()
    Dim dbExercise As Database
    
    Set dbExercise = CreateDatabase("Exercises1.accdb", dbLangGeneral)
    dbExercise.Close
End Sub

When calling this method, if you try to close a database that is currently closed, you would receive a 2467 error.

To support the creation of a database, the Access.Application object is equipped with a method named NewCurrentDatabase. Its syntax is:

Access.Application.NewCurrentDatabase(filepath, _
				      FileFormat, _
				      Template, _
				      SiteAddress, _
				      ListID)

Only the first argument is required. You can pass it as the name of the new database. Here is an example:

Private Sub cmdCreateDatabase_Click()
    Dim ROSH As Access.Application
    
    Set ROSH = CreateObject("Access.Application")
    ROSH.NewCurrentDatabase "Red Oak High School"
End Sub

If you do not specify an extension, the database engine would find out the latest version of Microsoft Access that is installed in your computer. If it is Microsoft Access 2007, a new database with extension .accdb would be created. Still, if you want, you can add an extension. If you have Microsoft Access 2007 but want to specify the version of database you want to create, pass the second argument. The second argument is a member of the AcNewDatabaseFormat enumeration. The available values are:

Here is an example:

Private Sub cmdCreateDatabase_Click()
    Dim ROSH As Access.Application
    
    Set ROSH = CreateObject("Access.Application")
    ROSH.NewCurrentDatabase "Red Oak High School", acNewDatabaseFormatAccess2007
End Sub

The other arguments are optional.

Once a database has been created, in order to use it, you must open it. To open a database in the Microsoft Office 12.0 Access Database Engine Library, you can call the OpenCurrentDatabase() method of the Access.Application class. Its syntax is:

Access.Application.OpenCurrentDatabase(filepath, Exclusive, bstrPassword)

The first argument can be the name of the database. If you provide (only) the name of the database, the engine would look for it in the same directory as the application that called it. Otherwise, you should provide the complete path to the database.

After using a database, you must close it to free the resources it was using. To close a database using the Microsoft Office 12.0 Access Database Engine Library, call the CloseCurrentDatabase() method. Its syntax is:

Access.Application.CloseCurrentDatabase()

As you can see, this method takes no argument. After calling this method, set its variable to Nothing. Here is an example:

Private Sub cmdCloseDatabase_Click()
    Dim ROSH As Access.Application
    
    Set ROSH = CreateObject("Access.Application")
    ROSH.CloseCurrentDatabase
    
    Set ROSH = Nothing
End Sub

When calling this method, the database must have been previously opened. If the database was not opened or it was already closed, you would receive a 2467 error.

In the previous lesson, saw that DAO is one of the most ancient and reliable libraries of Microsoft Access and used by applications published by companies other than Microsoft. Based on this, DAO supports all types of operations that can be performed on a database. One of these basic operations consist of creating a database.

To support the creation of a database, the DBEngne class of the DAO library is equipped with a method named CreateDatabase(). Its syntax is:

DBEngine.CreateDatabase(ByVal Name As String, _
			ByVal locale As String, _
			ByVal options As Variant) As Database

Because the DBEngine object is already recognized in the current database, you can omit it when calling the CreateDatabase() method. The arguments of this method are the same as those of the CreateDatabase() method of the Workspace class we reviewed for the Microsoft Access library.

When the CreateDatabase() method has finished, it returns a reference to the database that was created and you must obtain that reference. The database is recognized as the Database object of DAO. To get it, first declare a variable of type Database. Because the DAO object is implied, you can omit it in your declaration and simply use the Database object as type. To get a reference to the new database, use the SET operator and assign the returned value of the method to your Database variable. Here is an example that creates a new database named Exercise.accdb in the current folder:

Private Sub cmdCreate_Click()
    Dim db As DAO.Database

    Set db = CreateDatabase("Exercise.accdb", dbLangGeneral)
End Sub

To use a database, of course you must first open it. To support this operation, the DBEngine class of the DAO library provides the OpenDatabase() method whose syntax is:

Public Function DBEngine.OpenDatabase(ByVal Name As String, _
			              ByVal Options As Boolean, _
				      ByVal ReadOnly As Boolean, _
				      ByVal Connect As String) As Database

As you can see, the arguments of this method are exactly the same as those of the Workspace class of the Microsoft Access Object library. This means that the arguments follow the same description we saw already. Here is an example of calling the method:

Private Sub cmdOpenDatabase_Click()
    
    OpenDatabase("Example.accdb")
    
End Sub

When the DBEngine.OpenDatabase() method has been called, it returns a Database object. If you want to continue doing anything on the open database, you must get this Database value. Here is an example of getting it:

Private Sub cmdOpenDatabase_Click()
    Dim db As DAO.Database

    Set db = OpenDatabase("Example.accdb")
    
    . . . Now you can use the Database object

End Sub

Remember that the other arguments of the DBEngine.OpenDatabase() method follow the same description we saw for the Workspace class.

After using a database, you should close it to release the resources it was consuming. To do this, you can call the Close() method of the Database object. We saw that, to get a reference to the database that is being opened, you can get the return value of calling the OpenDatabase() method of the DBEngine class. This is done exactly as we saw for the Database class of the Microsoft Access Object library. Here is an example:

Private Sub cmdUseDatabase_Click()
    Dim db As DAO.Database

    Set db = OpenDatabase("Example.accdb")

    . . . Now you can use the Database object

    db.Close
End Sub

Before using ADO, you should first create a database using an application such as Microsoft Access or by other related means. Among the limitations of ADO in the beginning, there were security and even the ability to create a database without necessarily using a product from Microsoft. To address these issues, Microsoft created an additional library called  Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security, abbreviated ADOX. Before using ADOX, you must reference it in Microsoft Visual Basic. To do this, you can open the References dialog box from the Tools menu and select your latest version of Microsoft ADOX:

Like Microsoft Access' own library and like DAO, ADO relies on objects for its functionality. The most fundamental object used in ADO databases is ADOX. To use this object, you must create a reference to it. To do this, first declare a variable of type ADOX.

To support the creation of a database, the ADOX library provides the Catalog object. To access this object, the ADOX object is equipped with a class named Catalog. This class defines most of the objects you will need to use in your databases. To use the Catalog class, declare a variable for it and qualify it by using ADOX.Catalog. Here is an example:

Private Sub cmdAction_Click()
    Dim objCatalog As ADOX.Catalog
End Sub

Before using the Catalog object, you must specify that you need a reference to it, this can be done by initializing your variable with ADOX.Catalog using the New operator. Here is an example:

Private Sub cmdAction_Click()
    Dim objCatalog As ADOX.Catalog
    
    Set objCatalog = New ADOX.Catalog
End Sub

You can also refer to this property when declaring the variable. Here is an example:

Private Sub cmdAction_Click()
    Dim objCatalog As New ADOX.Catalog
End Sub

Whenever you create an ADO or ADOX object and while it is being used, it consumes resources. Therefore, after using the object, you should/must remove it from memory (if you don't, you may start seeing abnormal behavior on your computer after a while, as resources become scarce while other objects or applications are trying to use the same resources). To remove a resource from memory, after using it, assign it the Nothing constant.

ADO is probably the most popular library of Microsoft Access used outside of Microsoft. Most non-Microsoft programming environments that want to access a Microsoft Access database use this library. But as we saw in the previous lesson, you cannot create a database in ADO. Instead, you use its sister library the ADOX.

To create a database in ADOX, the ADOX library is equipped with a class named Catalog. To support database creation, the Catalog class is equipped with a method named Create. Its syntax is:

Catalog.Create(ByVal ConnectString As String)

This would be started as follows:

Private Sub cmdAction_Click()
    Dim objCatalog As ADOX.Catalog
    
    Set objCatalog = New ADOX.Catalog
    
    objCatalog.Create

    Set objCatalog = Nothing
End Sub

The Create() method of the Catalog class method takes as argument a string, also called a connection string, that defines the assignment to perform. The connection string follows the same description we reviewed for the Connection object.

If you are dealing with a Microsoft SQL Server database, specify the provider as SQLOLEDB. Here is an example:

Private Sub cmdAction_Click()
    Dim objCatalog As ADOX.Catalog
    
    Set objCatalog = New ADOX.Catalog
    
    objCatalog.Create "provider=SQLOEDB"

    Set objCatalog = Nothing
End Sub

If you are dealing with a Microsoft Access database, specify the provider as Microsoft.JET.OLEDB.4.0 (case insensitive). Here is an example:

Private Sub cmdAction_Click()
    Dim objCatalog As ADOX.Catalog
    
    Set objCatalog = New ADOX.Catalog
    
    objCatalog.Create "provider=Microsoft.Jet.OLEDB.4.0"

    Set objCatalog = Nothing
End Sub

Remember that you can include the name of the provider in single-quotes:

Private Sub cmdAction_Click()
    Dim objCatalog As ADOX.Catalog
    
    Set objCatalog = New ADOX.Catalog
    
    objCatalog.Create "provider='Microsoft.Jet.OLEDB.4.0'"

    Set objCatalog = Nothing
End Sub

If you are creating a SQL Server type of database, there are various other details you must provide to the connection string. For the rest of our lessons, we will use Microsoft JET.

To specify the second part of the connection string, which is the data source, use the same approach we saw for the Connection object. Here is an example:

Private Sub cmdCreateDatabase_Click()
    Dim objCatalog As ADOX.Catalog
    
    Set objCatalog = New ADOX.Catalog
    
    objCatalog.Create "provider=Microsoft.Jet.OLEDB.4.0;Data Source=Exercise.accdb"

    Set objCatalog = Nothing
End Sub

If you are creating a database and you provide (only) the name of the database, it would be created in the same folder as the application that called it. In the above example, the database would be created in the My Documents folder. If you want to create the database in a folder other than that of the application that called this method, provide a complete path  of the database. Here is an example:

Private Sub cmdCreateDatabase_Click()
    Dim objCatalog As ADOX.Catalog
    
    Set objCatalog = New ADOX.Catalog
    
    objCatalog.Create "provider=Microsoft.Jet.OLEDB.4.0;" & _
				"Data Source=C:\Exercises\Exercise.accdb"

    Set objCatalog = Nothing
End Sub

If you are creating a Microsoft Access Office 2007 database, specify the extension as .accdb. If you want to create a database compatible with previous versions of Microsoft Access, specify the extension as .mdb.

Remember that you can create the connection string and store it in a String variable. Then pass that variable to the Create() method. Here is an example:

Private Sub cmdAction_Click()
    Dim objCatalog As ADOX.Catalog
    Dim strCreator As String
    
    Set objCatalog = New ADOX.Catalog
    strCreator = "provider=Microsoft.Jet.OLEDB.4.0;'"
    strCreator = strCreator & "Data Source=C:\Exercises\Exercise.mdb'"
    
    objCatalog.Create strCreator

    Set objCatalog = Nothing
End Sub