Checking for Existence of a Field

Sometimes, you will want to add a new field to an existing table. Again, this code uses the OpenSchema method, but this time looks at the columns in the tables:

Function ColumnExists(WhichColumn, WhichTable) Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim WSOrig As Worksheet Dim WSTemp As Worksheet Dim fld As ADODB.Field ColumnExists = False

1 Path to Transfers.mdb is on menu

MyConn = ActiveWorkbook.Worksheets("Menu").Range("TPath").Value If Right(MyConn, 1) = Then

MyConn = MyConn & "transfers.mdb"


MyConn = MyConn & "\transfers.mdb" End If

Set cnn = New ADODB.Connection With cnn

.Provider = "Microsoft.Jet.OLEDB.4.0" .Open MyConn End With

Set rst = cnn.OpenSchema(adSchemaColumns)

Do Until rst.EOF

If LCase(rst!Column_Name) = LCase(WhichColumn) And _ LCase(rst!Table_Name) = LCase(WhichTable) Then ColumnExists = True

0 0

Post a comment