Database Creation

Microsoft ADO Ext.

Introduction

To get a database, you can either use one that exists already or you can create your own. ADO by itself doesn't provide the means to create a database. To create one, you can use the Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security, abbreviated ADOX. Before using ADOX, you must reference it in your Microsoft Visual Basic project. To do this, in the Microsoft Visual Basic Express studio, you can right-click the project in the Solution Explorer -> Add -> Add Reference... In the COM tab of the Add Reference dialog box, locate the Microsoft ADO Ext. 2.7 or 2.8 for DDL and Security:

After referencing the library, you can use its classes. The classes of the ADOX library are stored in a namespace named ADOX.

The Catalog Class

To create and manage various objects of a database, the ADOX namespace provides a class named Catalog. You can also use a class named CatalogClass. To use this class, you can first declare a variable of its type. Here is an example:

Private Sub btnCreateDatabase_Click(ByVal sender As System.Object, _

                ByVal e As System.EventArgs) Handles btnCreateDatabase.Click

	' Using the CatalogClass class of the ADOX namespace

        Dim catADOX As ADOX.CatalogClass

End Sub

After declaring the class, you can initialize it using the New operator:

Private Sub btnCreateDatabase_Click(ByVal sender As System.Object, _

                ByVal e As System.EventArgs) Handles btnCreateDatabase.Click

        Dim catADOX As ADOX.CatalogClass



        catADOX = New ADOX.CatalogClass

End Sub

Database Creation

The Connection String of the Catalog

To support database creation, the Catalog class is equipped with the Create() method. Its syntax is:

Create(ConnectionString As String)

The Create() method takes one argument usually referred to as the connection string. This string is made of sections separated by semi-colons. The formula used by these sections is:

Key1=Value1;Key2=Value2;Key_n=Value_n;

The first part of the connection string is called the provider. It is software that handles the database. To specify it, assign the desired name to the provider key. Here is an example:

Private Sub btnCreateDatabase_Click(ByVal sender As System.Object, _

                ByVal e As System.EventArgs) Handles btnCreateDatabase.Click

	' Using the Catalog class of the ADOX namespace

        Dim catADOX As ADOX.Catalog = New ADOX.Catalog



        catADOX.Create("Provider="

End Sub

There are various providers in the database industry. One of them is Microsoft SQL Server and it is represented by SQLOLEDB. If you want to create a Microsoft SQL Server database, specify this provider. Here is an example:

Private Sub btnCreateDatabase_Click(ByVal sender As System.Object, _

                ByVal e As System.EventArgs) Handles btnCreateDatabase.Click

	' Using the Catalog class of the ADOX namespace

        Dim catADOX As ADOX.Catalog = New ADOX.Catalog



        catADOX.Create("Provider=SQLOEDB"

End Sub

When creating this type of database, there are some other pieces of information you must provide in the connection string. Another provider is the Microsoft JET database engine represented as Microsoft.JET.OLEDB.4.0. To create a database for it,  specify its provider accordingly. Here is an example:

Private Sub btnCreateDatabase_Click(ByVal sender As System.Object, _

                ByVal e As System.EventArgs) Handles btnCreateDatabase.Click

	' Using the Catalog class of the ADOX namespace

        Dim catADOX As ADOX.Catalog = New ADOX.Catalog



        catADOX.Create("Provider=Microsoft.Jet.OLEDB.4.0"

End Sub

You can also include the name of the provider as its own string. To do that, you can include it in single-quotes:

Private Sub btnCreateDatabase_Click(ByVal sender As System.Object, _

                ByVal e As System.EventArgs) Handles btnCreateDatabase.Click

	' Using the Catalog class of the ADOX namespace

        Dim catADOX As ADOX.Catalog = New ADOX.Catalog



        catADOX.Create("Provider='Microsoft.Jet.OLEDB.4.0'"

End Sub

If you are creating a database, the second part of the connection string can be used to specify the path and the name of the database. This section must start with the Data Source key and assigned the path that consists of the drive and the folder(s). After the last folder, the name of the database must have the .mdb extension. For example, to create a database called Exercise that would reside in a folder called Exercises of the C: drive, you can specify the connection string as follows:

Private Sub btnCreateDatabase_Click(ByVal sender As System.Object, _

                ByVal e As System.EventArgs) Handles btnCreateDatabase.Click

        Dim catADOX As ADOX.Catalog



        catADOX = New ADOX.Catalog

        catADOX.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _

                             "Data Source=C:\Exercises\Exercise.mdb")

End Sub

You can also include the value of the Data Source key in single-quotes.

Instead of directly passing a string to the Create() method, you can first declare a String variable, initialize it with the necessary provider/data source, and then pass that string variable to the Create() method.