[2005] Small Access Database Schema Utility

This is just a small Database Schema Utility Class.

Code:

---

Option Strict On
Option Explicit On

Imports System.Data.OleDb

'This Code is given freely with no limitations on usage either personal or business.
'Code is provided "As Is" and has no warranties or guarantees associated with it.
'This is designed for versions of access prior to 2007, 2007 or later will need to modify
'at minimum the connection string.


Public Class AccessSchemaUtility

    'Global OledDBConnection
    Private globCN As OleDbConnection

    Protected Friend Function CreateNewConnection(ByVal FileName As String) As Boolean
        Dim cnStr As String = String.Empty
        Dim dbloc As String = String.Empty

        Try
            'If the database string stored in setting exists use it
            'Else find the database.  Once found setting gets changed.
            If System.IO.File.Exists(FileName) = False Then
                dbloc = "C:\New Folder\Air Enterprises\AE_Data.mdb"

            ElseIf System.IO.File.Exists(dbloc) = False AndAlso System.IO.File.Exists(FileName) = False Then
                'If file cannot be found then use an open file dialogue to find an appropriate database. 
                Dim ofd As New OpenFileDialog
                With ofd
                    .CheckFileExists = True
                    .Multiselect = False
                    .DefaultExt = ".mdb"
                    .Filter = "Database Files(*.mdb)|*.mdb"
                    .InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
                    .Title = "Find Database"
                    .ShowDialog()
                    dbloc = .FileName
                End With
            End If

            'Setup the connection string
            cnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbloc & ";"

            'If connection does not exist or is not currently open then create new connection
            If IsNothing(globCN) = True Then
                globCN = New OleDbConnection(cnStr)
                globCN.Open()
            End If

            Return True
        Catch ex As Exception
            MessageBox.Show(Err.Number & ": " & Err.Description)
            Return False
        End Try
    End Function

    Protected Friend Function TestConnection(ByVal cn As OleDbConnection) As Boolean
        'This is just a simple function to check if your database connection is open
        Try
            Select Case cn.State
                Case ConnectionState.Broken
                    Return False
                Case ConnectionState.Closed
                    Return False
                Case ConnectionState.Connecting
                    Return True
                Case ConnectionState.Executing
                    Return True
                Case ConnectionState.Fetching
                    Return True
                Case ConnectionState.Open
                    Return True
            End Select
        Catch ex As Exception
            Return False
        End Try
    End Function

    Protected Friend Sub DisconnectFromDB()
        Try
            'Destroys the connection.
            If Not IsNothing(globCN) Then
                globCN.Close()
                globCN.Dispose()
                globCN = Nothing
            End If
        Catch ex As Exception
            MessageBox.Show("Error Closing Database Connection " & Err.Number & ": " & Err.Description)
        End Try
    End Sub

    Protected Friend Sub AddColumnToDB(ByVal DBName As String, ByVal TableName As String, ByVal ColumnName As String, ByVal ColumnType As String)
        Dim cmd As OleDbCommand = Nothing
        Dim SelStr As String = String.Empty
        Dim dt As New DataTable

        Try
            If CreateNewConnection(DBName) = True Then

                'get list of fields in the access table
                dt = GetAccessTableColumnList(DBName, TableName)

                'IF nothing is returned then there was most likely an error and need to exit.
                If dt Is Nothing Then
                    Throw New Exception("Unable to find Column List for table " & TableName & ".")
                End If

                'Check for the specified field name, if found simply exit sub
                'This *IS* Case Sensitive.....
                For i As Integer = 0 To dt.Rows.Count - 1
                    If dt.Rows(i).Item(3).ToString = ColumnName Then
                        Exit Sub
                    End If
                Next

                'If it gets to this point then the field is not in the table and needs to be added
                cmd = New OleDbCommand

                SelStr = "ALTER Table " & TableName & " ADD COLUMN " & ColumnName & " " & ColumnType

                cmd.CommandType = CommandType.Text
                cmd.CommandText = SelStr
                cmd.Connection = globCN

                cmd.ExecuteNonQuery()
            Else
                MessageBox.Show("Cannot Connect to Database")
            End If
        Catch ex As Exception
            MessageBox.Show("Error Adding Column to Database " & Err.Number & ": " & Err.Description)
        Finally
            'cleanup objects
            If cmd IsNot Nothing Then cmd.Dispose()
            If dt IsNot Nothing Then dt.Dispose()

            'Close database connection
            DisconnectFromDB()
        End Try
    End Sub

    Protected Friend Function GetAccessTableList(ByVal DBFile As String) As DataTable
        Dim dt As New DataTable
        Try
            If CreateNewConnection(DBFile) = False Then
                Return Nothing
            Else
                dt = globCN.GetSchema("Tables")
                Return dt
            End If
        Catch ex As Exception
            MessageBox.Show("Error getting Access Table Schema: " & Err.Number & ": " & Err.Description, "Error Getting Access Table Schema")
            Return Nothing
        End Try
    End Function

    Protected Friend Function GetAccessTableColumnList(ByVal DBName As String, ByVal TableName As String) As DataTable
        Try
            If CreateNewConnection(DBName) = True Then
                Dim dt_field As DataTable = globCN.GetOleDbSchemaTable( _
                                OleDb.OleDbSchemaGuid.Columns, _
                                New Object() {Nothing, Nothing, TableName})

                Return dt_field
            Else
                Return Nothing
            End If
        Catch ex As Exception
            MessageBox.Show("Error getting Access Table Schema: " & Err.Number & ": " & Err.Description, "Error Getting Access Table Schema")
            Return Nothing
        End Try
    End Function

End Class

---

Here is a Sub to Remove a column from an access Table:

Code:

---

Friend Sub DeleteColumnFromDB(ByVal DBName As String, ByVal TableName As String, ByVal ColumnName As String)
        Dim cmd As OleDbCommand = Nothing
        Dim SelStr As String = String.Empty
        Dim dt As New DataTable
        Dim exists As Boolean = False

        Try
            If CreateNewConnection(DBName) = True Then
                'get list of fields in the access table
                dt = GetAccessTableColumnList(DBName, TableName)

                'Check for the specified field, if found set boolean
                For i As Integer = 0 To dt.Rows.Count - 1
                    If dt.Rows(i).Item(3).ToString = ColumnName Then
                        exists = True
                        Exit For
                    End If
                Next

                'If field exists in table then delete it
                If exists = True Then
                    cmd = New OleDbCommand

                    SelStr = "ALTER Table " & TableName & "DROP COLUMN " & ColumnName

                    cmd.CommandType = CommandType.Text
                    cmd.CommandText = SelStr
                    cmd.Connection = globCN

                    cmd.ExecuteNonQuery()
                Else
                    MessageBox.Show("")
                End If
            Else
                MessageBox.Show("Cannot Connect to Database")
            End If
        Catch ex As Exception
            MessageBox.Show(Err.Number & ": " & Err.Description)
        Finally
            'cleanup
            If cmd IsNot Nothing Then cmd.Dispose()
            If dt IsNot Nothing Then dt.Dispose()

            'Close db connection
            DisconnectFromDB()
        End Try
    End Sub

---

This can be added or inserted into the above class.

hello...
thanks for the utility. i am new to vb.net and have a question. does this require any specific UI to be on form?

I am not entirely sure what you mean by UI. This class has no form attached to it and can either be used as a standalone class or the subs/functions can be inserted into any code.

The only requirement is the Imports statement located at the top of the code in the first post. That statements makes that particular library more accessible throughout your code. If you did not have it, then you would need to type out the extended version (system.Data.OLEDB.[objectname].[propertyname]).

Does that answer your question?

Thanks and happy coding!! :wave:

D

Can someone tell me why this code (in first post) don't work, I just copy paste in my program but, nothing hapeneed! Thanks!:wave:

I have updated the class to include just about everything basic about creating or altering a database. Please see the attached. This class can be added to any VB 2005 project. To use this class, you will need to know where your database is located on the hard drive, network, wherever (not sure about online, I dont have any that are currently there to test with). This has been tested on Access 2003. If you are using 2007, you will need to change the connection string.

At the top of the attached class you will see this line:

Code:

---

Private DBLoc As String = String.Empty

---

While I personally like to use application settings, you can set this to the location of the access database. such as:

Code:

---

Private DBLoc As String = "C:\My Folder\MyAccessDB.mdb"

---

The following code is just a very generic sample of how to use this class.

Code:

---

    Private Sub SampleSub()
        'Name of the table to add
        Dim TableName As String = "MyTable"

        'List of Columns to add into table
        Dim Columnames As New List(Of String)

        'This declares a new instance of the class
        Dim X As New clsAccessDatabase

        'To keep this whole thing as simple as possible for the casual user, I am only really using Text/VarChar columns.

        'Add ColumnNames these will simply show up as text in your database
        Columnames.Add("MyColumn1")
        Columnames.Add("MyColumn2")
        Columnames.Add("MyColumn3")

        'Add New Table to database
        X.CreateTable(TableName, Columnames)

        'Add Column to an already existing table
        X.AddColumnToDB(TableName, "MyNewColumn", "Text")

        'If you need to alter the table then
        X.AlterColumnInDB(TableName, "MyColumn1")

        'Remove COlumn from table
        X.DeleteColumnFromDB("TableName", "MyColumn3")

        'Remove a Table
        X.DropTable(TableName)
    End Sub

---

Again, this is very basic (outside of retrieving schemas) but can be modified to become a very powerful addition to any application using access. I will do my best to answer any further questions.

D

ok, can you give me complete program with your code from last post? Thanks!:wave:

Uhmm...let me think about it a sec...... {No cursing allowed} No. Why?

1. I have given you EVERYTHING you need to complete what it is you asked for.
2. I do not have the rights to just put it up on the web.
3. Even if I did have the rights, why would I?
4. You have done NOTHING to help yourself and expect me/us to continue to help you?
5. This forum is a volunteer effort for collaboration, not do as you please.

Shall I continue?

Here I will tell you what. If you want more from me, then you are going to pay cash for it. Up Front. Then I will do your work for you. Up until that point, enjoy your "work".

when i copy code, nothinh hapeened, no one control are on form, code doesn't work!!!

Programming is not copying code and expecting everything to work magically. If you don't want to put any effort into trying to get the code to work then don't, but don't bother us about it! Give up programming already!

What you're doing is the same as what would happen if I were to decide to put a new engine in my car.
I would take an engine, drop it into the car somewhere where it fits (back seat maybe?), then whine why it doesn't work?!?! I put the engine in the car, so why doesn't it work?!

i will **** people now, which are helped on this forum!!!

I am getting errors on two things and was looking for some assistance.
The line

Code:

---

Dim ofd As New OpenFileDialog

---

is causing the class not to build.
It states in the error data that:

Code:

---

Error        1        Type 'OpenFileDialog' is not defined.       
C:\Documents and Settings\mike\My Documents\Visual Studio 2005\Projects\AccessSchemaUtility\AccessSchemaUtility\AccessSchemaUtility.vb        23       
32        AccessSchemaUtility

---

And also Name MessageBox is not declared..

this should be simple but I have not done a lot in .Net yet...

Thanks

It looks like you are not in a Windows Forms Project.

Doesn't he have the listing as Public Class?

The OpenFileDialog and the Messagebox are part of the windows.system.forms class and while it should be included in any project there is a chance that it is not. Mojo, make sure you have this statement at the top of the namespace/class/form(code):

Code:

---

Imports System.Windows.Forms

---

This will include all the classes, properties & methods included in it in your project. If this does not work, please let us know the type of project you are working with, and we can help you from there.

D

P.S. Also, you are working in VB2005, correct?? :)

I am using VB2005. I am sure I did not have that line at the top. When I get home tonight I will out this together and update you.

I meant to say that it should be included by default. I have never had a project that gave me errors with messagebox or filedialogbox, but I forget that each person has a different setup so it is possible. Let us know what happens.

D

Is this a form or a class file. I know they are technically the same thing, but...... I do not have it as a windows form project.

It does not matter because you can have classes within classes, forms, modules, etc. It was designed to be integrated with any project, so long as you have the appropriate imports statements.

D