CODEWITHSUNDEEP
excelvbams-access
Zaid Shaikh
Zaid Shaikh

Reputation: 49

How to check if a field exists in an Access Table using Excel VBA?

I want to check if a field exists in an Access Table. I am getting "Found" message even if the field does not exist.

Sub test()
    With Acon
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" + myFileNameDir
        .Properties("Jet OLEDB:Database Password") = "synpass"
        .Open
    End With

    With rs
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .ActiveConnection = Acon
        .Source = "Select Distinct [Term - Phases] from Sheet1"
        .Open
    End With

    On Error Resume Next

    If rs.Fields("Start_Date") Then
        MsgBox "Found"

    Else: MsgBox "not fund"

    End If
    On Error GoTo 0

    rs.Close Acon.Close
End Sub

Upvotes: 1

Views: 3459

Answers (1)

Mistella
Mistella

Reputation: 1728

Disclaimer I'm not used to working with fields from Microsoft.ACE.OLEDB and the following code is untested

Instead of using:

On Error Resume Next

If rs.Fields("Start_Date") Then
    MsgBox "Found"

Else: MsgBox "not fund"

End If
On Error GoTo 0

Try instead to assign the field to a variable, and then test:

Dim testField as Variant
On Error Resume Next
Set testField = rs.Fields("Start_Date")
On Error GoTo 0

If testField is Nothing Then 'Or is Empty
    MsgBox "Found"

Else: MsgBox "not fund"

End If

When On Error Resume Next is used around If Statements, and the condition causes the error, then the code assumes the condition is met and falls through.

Upvotes: 1

Related Questions