Microsoft Access Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesMicrosoft Access Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old May 26th, 2006, 06:23 AM
martin_g martin_g is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: May 2006
Location: Edinburgh
Posts: 12 martin_g User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 5 m 13 sec
Reputation Power: 0
Check if a field exists in a table (using VBA)

Hi all,

I am working on a bit of VBA code for MS Access and need to check if a certain field exists in a table.

My VBA skills are a bit rusty and google is no help at all.

Any help would be much appreciated.

Martin

Reply With Quote
  #2  
Old June 2nd, 2006, 05:32 AM
martin_g martin_g is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: May 2006
Location: Edinburgh
Posts: 12 martin_g User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 5 m 13 sec
Reputation Power: 0
I have found my own solution to this, and I thought I would post it in case anybody has the same problem.

I created a function to aid reusability:

Code:
' test if fieldName field exists in tableName table
Function FieldExists(ByVal fieldName As String, ByVal tableName As String) As Boolean
Dim db As Database
    Dim tbl As TableDef
    Dim fld As Field
    Dim strName As String
    Set db = CurrentDb
    Set tbl = db.TableDefs(tableName)
    For Each fld In tbl.Fields
        If fld.Name = fieldName Then
            FieldExists = True
            Exit For
        End If
    Next

    ' If FieldExists Then
    ' MsgBox "Field Name " + fieldName + " Exists in " + tableName
    ' Else
    ' MsgBox "Field Name Does Not Exist"
    ' End If
End Function

Reply With Quote
  #3  
Old April 23rd, 2010, 02:00 PM
compsci compsci is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Apr 2010
Posts: 1 compsci User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 11 m 14 sec
Reputation Power: 0
The previous example looks correct but is horribly inefficient. Try this one instead. Tested, working... HOWEVER; please excuse this stupid website for inserting spaces in the middle of my code (despite me using CODE tags...) Yes, this will break the code unless you fix it by removing the spaces e.g. from the middle of "fieldName"...

Code:
Public Function FieldExists(ByVal tableName As String, ByVal fieldName As String) As Boolean
On Error GoTo Failed
    FieldExists = (LenB(DBEngine(0)(0).TableDefs(tableName).Fields(f  ieldName).Name) > 0)
    Exit Function
Failed:
    If Err.number = 3265 Then Err.Clear ' Error 3265 : Item not found in this collection.
    FieldExists = False
End Function

Reply With Quote
  #4  
Old August 13th, 2012, 07:41 PM
savbill savbill is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Aug 2012
Posts: 1 savbill User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 41 m 38 sec
Reputation Power: 0
Quote:
Originally Posted by compsci
Try this one instead. Tested, working...

This is an excellent example posted above. The example has a problem which will produce a false positive if the table was deleted then re-created less the field being checked. To prevent the false 'True' you need to add a line to refresh the TableDefs Fields. Here's an example where I've added the Fields Refresh line to ensure accurate result.
Code:
Public Function FieldExists(ByVal tableName As String, ByVal fieldName As String) As Boolean
Dim nLen As Long

On Error GoTo Failed
    With DBEngine(0)(0).TableDefs(tableName)
        .Fields.Refresh
        nLen = Len(.Fields(fieldName).Name)
        If nFldLen > 0 Then FieldExists = True
    End With
    Exit Function
Failed:
    If Err.Number = 3265 Then Err.Clear ' Error 3265 : Item not found in this collection.
    FieldExists = False
End Function

Reply With Quote
  #5  
Old January 23rd, 2014, 07:08 PM
asap168 asap168 is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2014
Posts: 1 asap168 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 13 m 11 sec
Reputation Power: 0
Debug: just a typo

Public Function FieldExists(ByVal tableName As String, ByVal fieldName As String) As Boolean
Dim nLen As Long

On Error GoTo Failed
With DBEngine(0)(0).TableDefs(tableName)
.Fields.Refresh
nLen= Len(.Fields(fieldName).Name)

If nLen > 0 Then FieldExists = True

End With
Exit Function
Failed:
If Err.Number = 3265 Then Err.Clear ' Error 3265 : Item not found in this collection.
FieldExists = False
End Function

Reply With Quote
  #6  
Old March 10th, 2016, 10:05 AM
dalepmay dalepmay is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Mar 2016
Posts: 1 dalepmay User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 m 27 sec
Reputation Power: 0
I found an even faster way.

Code:
Function DoesFieldExist(sField as string, sTable as string) as Boolean
   Err.Clear
   If (DCount(sField, sTable) = 0) And Err Then DoesFieldExist = False Else DoesFieldExist = True
End Function

Reply With Quote
  #7  
Old October 18th, 2017, 10:12 AM
usmanakbar usmanakbar is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Oct 2017
Posts: 1 usmanakbar User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 9 m 24 sec
Reputation Power: 0
same code with correction

Please don't mind the indentation here:


Function DoesFieldExist(sField As String, sTable As String) As Boolean
Err.Clear
DoesFieldExist = False
On Error GoTo setfalse:
If (DCount(sField, sTable) = 0) And Err Then DoesFieldExist = False Else DoesFieldExist = True

setfalse:

End Function

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMicrosoft Access Development > Check if a field exists in a table (using VBA)


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap