Create Microsoft Access Database Programmatically using VB.NET

• Download source - 104.9 KB

Introduction

This tip shows you how to create an Access Database programmatically by using VB.NET without Microsoft Office.

But you have to install this Microsoft Access Database Engine 2013 runtime for this sample to work properly, or any version you prefer. You can download it from Microsoft Access Database Engine 2013.

When starting a new project, do not forget to import:

• System.Data
• System.Data.OleDb
• System.Configuration, add at References > Assemblies > Framework
• Microsoft Office 15.0 Access Database Engine Object Library, add at References > COM
  • Microsoft.Office.Interop.Access.Dao

If you installed x86 Microsoft Access Database Engine 2013 runtime, do not forget to set Compile > Target CPU > x86. AnyCPU only gives you a headache.

Background

I came up with this idea because my web hosting server had a compatibility issue with the Access Database created by the local computer. Code implementation is almost the same for Winform and ASP.NET project, it just needs some work.

Using the Code

So, let's start looking into the coding.

We start with the app.config. These are the few settings to add into the app.config.

<appSettings>
    <!-- database root-->
    <add key="dbroot" value="database"/>
    <!-- database root-->
    <!-- Database_Provider -->
    <add key="Database_Provider"
    value="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%mdbfilepath%;
    Jet OLEDB:Database Password=%mdbfilepasswords%;" />
    <!-- Database_NewDatabaseType value is 1,1.1,2,3,4,12,14,15 database version, runtime version-->
    <add key="Database_NewDatabaseType" value="14" />
    <!-- Database_Provider -->

    <!-- database_mdb filename only without file extension -->
    <add key="Database_sample1" value="sample1" />
    <add key="Database_sample2" value="sample2" />
    <!-- database_mdb filename only without file extension -->
  </appSettings >

dbroot is the folder where you keep the database file, just the folder name. In this case, it is database.

    <!-- database root-->
    <add key="dbroot" value="database"/>
    <!-- database root-->

Database_Provider, you can set the database version at Database_NewDatabaseType.

    <!-- Database_Provider -->
    <add key="Database_Provider" value="Provider=Microsoft.ACE.OLEDB.12.0;
    Data Source=%mdbfilepath%;Jet OLEDB:Database Password=%mdbfilepasswords%;" />
    <!-- Database_NewDatabaseType value is 1,1.1,2,3,4,12,14,15 database version, runtime version-->
    <add key="Database_NewDatabaseType" value="14" />
    <!-- Database_Provider --> 

Name the Database file and the key. In this case, the key is Database_sample1 and the filename is sample1. You have to add more items here if you have more database files.

    <!-- database_mdb filename only without file extension -->
    <add key="Database_sample1" value="sample1" />
    <add key="Database_sample2" value="sample2" />
    <!-- database_mdb filename only without file extension -->

Now, here is the code.

To create a new database, all you need to do is just one line of code.

      CloudAccessDatabase.CreateNewDatabase(CloudAccessDatabase.DatabaseType.sample1)
      CloudAccessDatabase.CreateNewDatabase(CloudAccessDatabase.DatabaseType.sample2)

But, before that, you have some work to do.

Step 1: Add your database id. If you have 3,4,5 database, just put any name you like, example sample3=2.

       Public Enum DatabaseType
        sample1 = 0
        sample2 = 1
       End Enum  

Step 2: Get the database file name from the app.config.

    Shared Function ConnDatabaseFilePath(stype As DatabaseType) As String

        Dim dbroot As String = CloudAppSettings.GetValidDatabaseRoot
        Dim dbfilename As String = ""

        'create database folder
        If Directory.Exists(dbroot) = False Then
            Directory.CreateDirectory(dbroot)
        End If

        'get the access file name from app.config
        Select Case stype
            Case DatabaseType.sample1
                dbfilename = CloudAppSettings.GetConfigAppSettingsByName("Database_sample1")
            Case DatabaseType.sample2
                dbfilename = CloudAppSettings.GetConfigAppSettingsByName("Database_sample2")
        End Select

        dbfilename = String.Format("{0}{1}", dbfilename, ".accdb")
        dbroot = String.Format("{0}{1}", dbroot, dbfilename)

        Return dbroot
    End Function  

Step 2.1: Only this section you need to change if have any new database added.

    Select Case stype
            Case DatabaseType.sample1
                dbfilename = CloudAppSettings.GetConfigAppSettingsByName("Database_sample1")
            Case DatabaseType.sample2
                dbfilename = CloudAppSettings.GetConfigAppSettingsByName("Database_sample2")
        End Select  

Step 3: Write the database table command. If your DatabaseType Enum has DatabaseType.sample3, you have to write something for it.

    Shared Function CommandString(sType As DatabaseType) As ArrayList

        Dim result As New ArrayList
        Select Case sType
            Case DatabaseType.sample1
                result.Add("CREATE TABLE sample1 ([id] COUNTER IDENTITY(1,1),
                _[SessionId] VARCHAR (40), [SessionDate] DATETIME, _
                [SessionName] LONGTEXT, CONSTRAINT PKSessions PRIMARY KEY (SessionId))")
            Case DatabaseType.sample2
                result.Add("CREATE TABLE Roles ([id] COUNTER IDENTITY(1,1), _
                [Rolename] LONGTEXT, [ApplicationName] LONGTEXT, _
                CONSTRAINT PKRoles PRIMARY KEY (Rolename, ApplicationName))")
                result.Add("CREATE TABLE UsersInRoles ([id] COUNTER IDENTITY(1,1), _
                [Username] LONGTEXT, [Rolename] LONGTEXT, [ApplicationName] LONGTEXT, _
                CONSTRAINT PKUsersInRoles PRIMARY KEY (Username, Rolename, ApplicationName))")
        End Select
        Return result
    End Function   

So, now you understand a bit?

The Core

All the preparation above is for this code.

1. Get the file path.
2. Get the connection string.
3. Create OleDbConnection.
4. Check if database already exists.
5. AccessCreateDB, create the database file.
6. Create Database Table, either one or more table.
7. Done.

   Public Shared Sub CreateNewDatabase(sType As DatabaseType)
        Dim dbfile As String = ConnDatabaseFilePath(sType)
        Dim con As String = ConnSettings(sType)
        Dim conn As OleDbConnection = New OleDbConnection(con)
        Try
            If File.Exists(dbfile) = True Then
                'database already exist
            Else
                AccessCreateDB(dbfile)
                'create table
                If CommandString(sType).Count > 1 Then
                    conn.Open()
                    For items = 0 To CommandString(sType).Count - 1
                        Dim cmdCreate As New OleDbCommand
                        With cmdCreate
                            .Connection = conn
                            .CommandText = CommandString(sType).Item(items).ToString
                        End With
                        cmdCreate.ExecuteNonQuery()
                    Next
                    conn.Close()
                Else
                    Dim cmdCreate As New OleDbCommand
                    With cmdCreate
                        .Connection = conn
                        .CommandText = CommandString(sType).Item(0).ToString
                    End With
                    conn.Open()
                    cmdCreate.ExecuteNonQuery()
                    conn.Close()
                End If

            End If
        Catch ex As OleDbException
            MsgBox(ex.Message, MsgBoxStyle.Information, "OleDbException")
        End Try
    End Sub