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