MS Access ADO VBA Check if Field Exists for
versions 2000, 2002, 2003, 2007
Go here To
Check if Table exists in VBA.
Go here To
Update a Field in VBA.
Go here To
Check if Field exists in VBA using DAO.
Follow the
Delete a Table with VBA Link to delete a Table in VBA.
Go here To
Check if Query Exsists in VBA.
|
The VBA
ADO Check for Field Sample is available for download
here in a zipped MS Access Database.
The following code snippet will use VBA to check if a field exists within a
given table using ADO.
Function
ADO_FieldExsists(strField
As String
, strTable
As String
)
As Boolean
' References: Microsoft Access 11.0 Object Library,
' Microsoft Activex Data Objects
2.1 Library
' Set references by Clicking Tools and Then References in the Code
View window
' Checks for a field in ADO
'
' strField: Name of Target Field
' strTable: Name of Target Table
'
' Returns True on success, False Otherwise
' USAGE: ADO_FieldExsists ("strField", "strTable" )
Dim
oRecordset
As New
ADODB.Recordset
Dim
oConn
As New
ADODB.Connection
Dim
strSQL
As String
On Error GoTo
errhandler
' Create connection
Dim
AccConn
As String
AccConn =
"Driver={Microsoft Access Driver (*.mdb)};" &
_
"Dbq=VBA_Function.mdb;" &
_
"DefaultDir=" &
CurrentProject.path
& ";" &
_
"Uid=Admin;Pwd=;"
oConn.Open AccConn
'Make the SQL and return only the Top record if the field exsists
strSQL =
"SELECT top 1 [" &
strTable
& "].[" &
strField
& "] FROM " &
strTable
& ";"
Set
oRecordset = oConn.Execute(strSQL)
oRecordset.MoveFirst
'If no error continue and report
If
oRecordset.Fields(
0
).Name = strField
Then
'If our name exists report True
ADO_FieldExsists =
True
Else
'In case table is opened and Fieldname is changed and Prefixed with "Expr1"
'Our names do not match and do not exist, report
false
ADO_FieldExsists =
False
End If
If
oRecordset.State = adStateOpen
Then
oRecordset.Close
End If
ExitHere:
If
oConn.State = adStateOpen
Then
oConn.Close
End If
Set
oConn =
Nothing
Set
oRecordset =
Nothing
'Notify the user of the result.
MsgBox ADO_FieldExsists
Exit Function
errhandler:
'There is an error report false
ADO_FieldExsists =
False
With
Err
MsgBox
"Error " &
.Number
&
vbCrLf
&
.Description, _
vbOKOnly
Or
vbCritical,
"ADO_FieldExsists"
End With
Resume
ExitHere
End Function
|
|
For further help or suggestions go here:
MS Access tutorials, Tables, Queries and later: Forms
You may post this tutorial on your website or in a forum. If you do please
maintain a Link to Eraseve AP.
For further MS Access tutorials go here:
MS Access tutorials
Go here To
Check if Table exists in VBA.
Follow the
Set Autonumber Field with VBA Link to Set a Field to
Autonumber.
