Creating a Database

The Catalog Class

Module Central

    Sub Main()
        Dim catDatabase As ADOX.Catalog

        catDatabase = New ADOX.CatalogClass
        catDatabase.Create("")
    End Sub

End Module

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:

Module Central

    Sub Main()
        Dim catDatabase As ADOX.Catalog

        catDatabase = New ADOX.CatalogClass
        catDatabase.Create("Provider=")
    End Sub

End Module

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:

Module Central

    Sub Main()
        Dim catDatabase As ADOX.Catalog

        catDatabase = New ADOX.CatalogClass
        catDatabase.Create("Provider=SQLOLEDB;")
    End Sub

End Module

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:

Module Central

    Sub Main()
        Dim catDatabase As ADOX.Catalog

        catDatabase = New ADOX.CatalogClass
        catDatabase.Create("Provider=Microsoft.Jet.OLEDB.4.0;")
    End Sub

End Module

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 will be asked to provide a data source for your database. In this case, provide the complete path followed by the name of the database.

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 Exercise1 that would reside in a folder called Programs on the C: drive, you can specify the connection string as follows:

Module Central

    Sub Main()
        Dim catDatabase As ADOX.Catalog

        catDatabase = New ADOX.CatalogClass
        catDatabase.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                              "Data Source='C:\Programs\Exercise1.mdb'")
        Console.WriteLine("A new Microsoft JET database named " & _
                           "Exercise1.mdb has been created")
    End Sub

End Module

This would produce:

A new Microsoft JET database named Exercise1.mdb has been created
Press any key to continue . . .

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. You can also initialize the provider in a string variable and then pass both string variables to the Create() method. Here is an example:

Module Central

    Sub Main()
        Dim catDatabase As ADOX.Catalog
        Dim strConnection As String
        Dim strDatabase As String

        catDatabase = New ADOX.CatalogClass

        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
        strDatabase = "Data Source='C:\Programs\Exercise1.mdb';"
        catDatabase.Create(strConnection & strDatabase)

        Console.WriteLine("A new Microsoft JET database named " & _
                           "Exercise1.mdb has been created")
    End Sub

End Module

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 Add button. This would launch a wizard. In the first page of the Create New Data Source wizard, click Microsoft Access Driver (*.mdb):

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. Here is an example:

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 it. 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.

As mentioned earlier, Microsoft ActiveX Data Objects, or ADO, is a library used to create databases. Before using this library, you must import it in your application. To do this, you can right-click the References node in the Solution Explorer for your project and click Add Reference... In the COM tab of the Add Reference dialog box, select the latest Microsoft ActiveX Data Object Library:

And click OK. This would add the ADO reference to your project.

The ADO library is represented in the .NET Framework by the ADODB namespace. It contains various classes that you can access by qualifying them through this namespace. As done with the other classes that belong to a namespace, if you want, in the top section of the file where you would use ADO, you can add the ADODB namespace. Here is an example:

Imports ADODB

Module Central

    Sub Main()

    End Sub

End Module