VB.NET Create MS-Access database and table

kevininstructor · 01-02-2011, 07:08 AM

Please note that the methods used to create the database will work in VS2005 but the layout of the attached project is VS2008 so if you are using a version less than VS2008 you will need to hack it up (excuse the pun).

The attached project done in VS2008, VB.NET demonstrates how to create a new Microsoft Access database using ADOX.

Figure 1 shows the creation of a table with an auto-incrementing primary key and two additional fields with a constraint on the primary key. Figure 2 shows a row added to the newly added table

One of the best things in working with data is the BindingSource class. With that said a extremely super example is shown in figure 3 which filters the underlying data source, a DataTable just created.

Caveat: I generally do not create Microsoft Access files this was as it is easier to simply include a blank Access database with my project and copy it to a working folder then add tables as needed but I know some programmers rather create them on the fly which is why I am presenting this here.

Figure 1

Code:
Dim cmd As New OleDb.OleDbCommand( _
            <text>
                CREATE TABLE persons ([Identifier] int identity ,
                                      UserName NVarchar(50), 
                                      [Password] NVarchar(50),
                                      CONSTRAINT [pk_AutoId] PRIMARY KEY ([Identifier])) 
            </text>.Value, cn)

Figure 2

Code:
cmd.CommandText = _
            <Text>
                INSERT INTO persons 
                (UserName,[Password])
                VALUES 
                ("Gallagher","pass1word")
            </Text>.Value
            cmd.ExecuteNonQuery()

Figure 3

Code:
Private Sub cmdFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdFilter.Click

        If bsDataSource.DataSource IsNot Nothing Then
            If bsDataSource.Filter = "" Then
                bsDataSource.Filter = "UserName like 'G%'"
                cmdFilter.Text = "Remove Filter"
            Else
                bsDataSource.Filter = ""
                cmdFilter.Text = "Set Filter"
            End If
        End If
    End Sub

kevininstructor · 07-31-2011, 01:24 AM

(01-07-2011, 04:50 AM)The 7th Sage Wrote: Ah good ol ADO. Nostalgia. Nice share.

ADOX is the only method to create an MS-Acces database but of course there are other ways to do this that many do not consider. Take a blank MS-Access database and add it to your resources and extract it to disk when needed or have a folder that contains the blank database and when needed copy it to the locate it will be used.

The code presented will also work on existing databases be it .mdb or accdb.