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