Results 1 to 19 of 19

Thread: [2005] Small Access Database Schema Utility

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Aug 2006
    Location
    In my head
    Posts
    913

    [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
    Platforms of choice: Visual Studio 2005/2008 Professional : Visual Studio 2010 Enterprise : PHP - Notepad++/WAMP

    Please Rate If I helped you.
    Please remember to mark threads as closed if your issue has been resolved.

    Reserved Words in Access | Connection Strings

  2. #2

    Thread Starter
    Fanatic Member
    Join Date
    Aug 2006
    Location
    In my head
    Posts
    913

    Re: [2005] Small Access Database Schema Utility

    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.
    Platforms of choice: Visual Studio 2005/2008 Professional : Visual Studio 2010 Enterprise : PHP - Notepad++/WAMP

    Please Rate If I helped you.
    Please remember to mark threads as closed if your issue has been resolved.

    Reserved Words in Access | Connection Strings

  3. #3
    New Member
    Join Date
    Aug 2008
    Posts
    1

    Re: [2005] Small Access Database Schema Utility

    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?

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Aug 2006
    Location
    In my head
    Posts
    913

    Re: [2005] Small Access Database Schema Utility

    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!!

    D
    Platforms of choice: Visual Studio 2005/2008 Professional : Visual Studio 2010 Enterprise : PHP - Notepad++/WAMP

    Please Rate If I helped you.
    Please remember to mark threads as closed if your issue has been resolved.

    Reserved Words in Access | Connection Strings

  5. #5
    Addicted Member
    Join Date
    Jan 2009
    Posts
    138

    Re: [2005] Small Access Database Schema Utility

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

  6. #6

    Thread Starter
    Fanatic Member
    Join Date
    Aug 2006
    Location
    In my head
    Posts
    913

    Re: [2005] Small Access Database Schema Utility

    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
    Attached Files Attached Files
    Platforms of choice: Visual Studio 2005/2008 Professional : Visual Studio 2010 Enterprise : PHP - Notepad++/WAMP

    Please Rate If I helped you.
    Please remember to mark threads as closed if your issue has been resolved.

    Reserved Words in Access | Connection Strings

  7. #7
    Addicted Member
    Join Date
    Jan 2009
    Posts
    138

    Re: [2005] Small Access Database Schema Utility

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

  8. #8

    Thread Starter
    Fanatic Member
    Join Date
    Aug 2006
    Location
    In my head
    Posts
    913

    Angry Re: [2005] Small Access Database Schema Utility

    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".
    Platforms of choice: Visual Studio 2005/2008 Professional : Visual Studio 2010 Enterprise : PHP - Notepad++/WAMP

    Please Rate If I helped you.
    Please remember to mark threads as closed if your issue has been resolved.

    Reserved Words in Access | Connection Strings

  9. #9
    Addicted Member
    Join Date
    Jan 2009
    Posts
    138

    Re: [2005] Small Access Database Schema Utility

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

  10. #10
    PowerPoster
    Join Date
    Apr 2007
    Location
    The Netherlands
    Posts
    5,070

    Re: [2005] Small Access Database Schema Utility

    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?!

  11. #11
    Addicted Member
    Join Date
    Jan 2009
    Posts
    138

    Re: [2005] Small Access Database Schema Utility

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

  12. #12
    Fanatic Member
    Join Date
    Aug 2005
    Location
    Wisconsin
    Posts
    788

    Re: [2005] Small Access Database Schema Utility

    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

  13. #13

  14. #14
    Fanatic Member
    Join Date
    Aug 2005
    Location
    Wisconsin
    Posts
    788

    Re: [2005] Small Access Database Schema Utility

    Doesn't he have the listing as Public Class?

  15. #15

    Thread Starter
    Fanatic Member
    Join Date
    Aug 2006
    Location
    In my head
    Posts
    913

    Re: [2005] Small Access Database Schema Utility

    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??
    Platforms of choice: Visual Studio 2005/2008 Professional : Visual Studio 2010 Enterprise : PHP - Notepad++/WAMP

    Please Rate If I helped you.
    Please remember to mark threads as closed if your issue has been resolved.

    Reserved Words in Access | Connection Strings

  16. #16
    Fanatic Member
    Join Date
    Aug 2005
    Location
    Wisconsin
    Posts
    788

    Re: [2005] Small Access Database Schema Utility

    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.

  17. #17

    Thread Starter
    Fanatic Member
    Join Date
    Aug 2006
    Location
    In my head
    Posts
    913

    Re: [2005] Small Access Database Schema Utility

    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
    Platforms of choice: Visual Studio 2005/2008 Professional : Visual Studio 2010 Enterprise : PHP - Notepad++/WAMP

    Please Rate If I helped you.
    Please remember to mark threads as closed if your issue has been resolved.

    Reserved Words in Access | Connection Strings

  18. #18
    Fanatic Member
    Join Date
    Aug 2005
    Location
    Wisconsin
    Posts
    788

    Re: [2005] Small Access Database Schema Utility

    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.

  19. #19

    Thread Starter
    Fanatic Member
    Join Date
    Aug 2006
    Location
    In my head
    Posts
    913

    Re: [2005] Small Access Database Schema Utility

    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
    Platforms of choice: Visual Studio 2005/2008 Professional : Visual Studio 2010 Enterprise : PHP - Notepad++/WAMP

    Please Rate If I helped you.
    Please remember to mark threads as closed if your issue has been resolved.

    Reserved Words in Access | Connection Strings

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width