[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
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.
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?
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!! :wave:
D
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!:wave:
1 Attachment(s)
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
Re: [2005] Small Access Database Schema Utility
ok, can you give me complete program with your code from last post? Thanks!:wave:
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".
Re: [2005] Small Access Database Schema Utility
when i copy code, nothinh hapeened, no one control are on form, code doesn't work!!!
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?!
Re: [2005] Small Access Database Schema Utility
i will **** people now, which are helped on this forum!!!
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
Re: [2005] Small Access Database Schema Utility
It looks like you are not in a Windows Forms Project.
Re: [2005] Small Access Database Schema Utility
Doesn't he have the listing as Public Class?
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?? :)
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.
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
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.
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