Determine if a particular column exists in MS Access table using VB.NET

In this article we will see how to determine if a particualar column exists in MS Access Database Table.

There are two methods:

1. Using DAO

2. Using OLEDB .NET provider


Using DAO

Prerequisites:

1. Need to add reference to Microsoft DAO 3.6 Object libraray

2. Add Reference -> COM tab -> Microsoft DAO 3.6 Object libraray

3. Imports DAO in the top of the form



'Determine if ColumnName Column exists in the given table

Function ColumnExists(ByVal ColumnName As String, ByVal AccessTableName As String) As Boolean

'Declaraion of Variables

Dim AccessDB As Database
Dim AccessTblDef As TableDef
Dim col As Field
Dim dbe As New DBEngine
Dim bColumnFound As Boolean = False

Try
AccessDB = dbe.OpenDatabase("C:\MyTestDB.mdb")

AccessTblDef = db.TableDefs(AccessTableName)

For Each col In tbl.Fields

'If column exists

If col.Name = ColumnName Then

bColumnFound = True

Exit For

End If

Next

Catch ex As Exception

Return bColumnFound

End Try

Return bColumnFound

End Function





Explanation

The DAO namespace contains data types Database, Tabledef and DBEngine

The Database type is used to assign access db.

A DAO tabledef, is an object that defines the structure of a base table or an attached table.

A base table is a table in a Microsoft Jet (.MDB) database. You can manipulate the structure of a base table using DAO objects or data definition language (DDL) SQL statements, and you can use recordsets and action queries to modify data in a base table.


The "Name" Field of fields of table def represents the column names.

Thus we can compare the name of the field f table def with the column name to search for.



Using OLEDB .NET Provider


Prerequisites:

Need to import reference to System.Data.OleDb Name space


Function ColumnExistsOLEDB(ByVal ColumnName As String, ByVal AccessTableName As String) As Boolean

'Declaration of variables

Dim OLEDBConn As New System.Data.OleDb.OleDbConnection

Dim SchemaTable As DataTable

Dim sConnString As String

Dim bColumnFound As Boolean = False



'Connection String - OLEDB

sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyTestDB.mdb"

OLEDBConn.ConnectionString = sConnString

Try

'Open the database
OLEDBConn.Open()

'Retrieve schema information about AccessTableName Columns.

SchemaTable =

OLEDBConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, AccessTableName})

Dim i As Integer

Dim ColName As String

For i = 0 To SchemaTable.Rows.Count - 1

ColName = SchemaTable.Rows(RowCount)!COLUMN_NAME.ToString

'If the column exists

If ColName = ColumnName Then

bColumnFound = True

Exit For


End If

Next

Catch ex As Exception

Return bColumnFound

End Try

Return bColumnFound

End Function


Explanation

The GetOleDbSchemaTable returns schema information from a data source as indicated by a GUID, and after applying the specified restrictions.

Using this function we can get the schema definition of the specified table(Emp2008 in this case)

The COLUMN_NAME property of the schema does the trick and we can compare it with the column to search for.


USAGE Example


'Function to call ColumnExists with Access table name and column name to search for

Private Sub CheckForColumn ()

Dim bColumnExists As Boolean = False

'Column Name - EmpRefNo
'Table Name - Emp2008

bColumnExists = ColumnExists("EmpRefNo", "Emp2008")

If bColumnExists Then

MsgBox("Field Name EmpRefNo Exists in Emp2008 Table")
Else

MsgBox("Field Name EmpRefNo Does Not Exist in Emp2008 Table")

End If

End Function


'Function to call ColumnExists with Access table name and column name to search for

Private Sub CheckForColumn ()

Dim bColumnExists As Boolean = False

'Column Name - EmpRefNo
'Table Name - Emp2008

bColumnExists = ColumnExistsOLEDB("EmpRefNo", "Emp2008")

If bColumnExists Then

MsgBox("Field Name EmpRefNo Exists in Emp2008 Table")
Else

MsgBox("Field Name EmpRefNo Does Not Exist in Emp2008 Table")

End If

End Function



Though the approach is differenent, effectively the result is same.