Introduction
These are two functions I wrote in VB.NET using ADO.NET to check and see if a table or a field exists in a database. It can work with MS Access, or SQL Server, or any other OLE database.
Background
Checking if a table or a field exists in an Access database should be a very simple task, but it can become very complicated with ADO.NET. With DAO or ADO in VB 6.0, this was an extremely easy task. Those who have used it will agree. So, I am posting these functions to help other programmers. Hopefully, it will help out some.
Using the code
Here are the functions:
Public Function DoesTableExist(ByVal tblName As String, ByVal cnnStr As String) As Boolean
Dim dbConn As New OleDbConnection(cnnStr)
dbConn.Open()
Dim restrictions(3) As String
restrictions(2) = tblName
Dim dbTbl As DataTable = dbConn.GetSchema("Tables", restrictions)
If dbTbl.Rows.Count = 0 Then
DoesTableExist = False
Else
DoesTableExist = True
End If
dbTbl.Dispose()
dbConn.Close()
dbConn.Dispose()
End Function
Public Function DoesFieldExist(ByVal tblName As String, _
ByVal fldName As String, _
ByVal cnnStr As String) As Boolean
Dim dbConn As New OleDbConnection(cnnStr)
dbConn.Open()
Dim dbTbl As New DataTable
Dim strSql As String = "Select TOP 1 * from " & tblName
Dim dbAdapater As New OleDbDataAdapter(strSql, dbConn)
dbAdapater.Fill(dbTbl)
Dim i As Integer = dbTbl.Columns.IndexOf(fldName)
If i = -1 Then
DoesFieldExist = False
Else
DoesFieldExist = True
End If
dbTbl.Dispose()
dbConn.Close()
dbConn.Dispose()
End Function