DataBase - Checking for Existing Field in Table

Asked By RedHeadedMonster via AccessMonster.com

03-Feb-10 01:26 PM

Ok I have the following code:

Function CheckIfFieldExist(TableName As String, FieldName As String) As
Boolean
On Error GoTo CheckIfFieldExist_Err
'  Function that check if a field exist in the table
'  Return True if exist , and False if does not exist
Dim I As String
CheckIfFieldExist = True
I = Application.CurrentDb.TableDefs(TableName).Fields(FieldName).Name
Exit Function
CheckIfFieldExist_Err:
If Err = 3265 Then
CheckIfFieldExist = False
Else
MsgBox Error
End If

End Function

this is the other half of the code:

'Late

If CheckIfFieldExist("tblCDRLMetricsFinal2", "Late") Then
' Exist
Else
' Doesn't Exist

Dim dbCurLate As Database
Dim tdfLate As TableDef
Dim fldLate As Field

Set dbCurLate = CurrentDb()
Set tdfLate = dbCurLate.TableDefs("tblCDRLMetrics2Final")
Set fldLate = tdfLate.CreateField("Late", dbInteger)

tdfLate.Fields.Append fldLate
tdfLate.Fields.Refresh
End If

Problem is....regardless or whether or not "Late" exists (True or False The
code tries to create the "Late" field...of course if its already there I get
an error message.  I dont want it to try and create if its there....I have
looked at this code until Im crosseyed...Someone see what the hell Im doing
wrong?
Thanx!
RHM

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1

03-Feb-10 02:47 PM

A couple things come to mind.

1.  I never set a function to True as the default and then switch it to
false.  I always do the inverse to avoid false positives.  Below is what I
would do:

Function CheckIfFieldExist(TableName As String, FieldName As String) As
Boolean
'  Function that check if a field exist in the table
'  Return True if exist , and False if does not exist
Dim I    As String

On Error GoTo Error_Handler

CheckIfFieldExist = False
I = Application.CurrentDb.TableDefs(TableName).Fields(FieldName).Name
CheckIfFieldExist = True   'If we made it to hear without triggering an
'error all is good

Error_Handler_Exit:
On Error Resume Next
Exit Function

Error_Handler:
If Err = 3265 Then   'Field not found in the specified table
Else
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf
& "Error Number: " & _
Err.Number & vbCrLf & "Error Source: CheckIfFieldExist" & vbCrLf &
Err.Description, vbCritical, "An Error has Occured!"
End If
Resume Error_Handler_Exit
End Function


Sub AddField2Tbl(TableName As String)
Dim dbCurLate  As Database
Dim tdfLate    As TableDef
Dim fldLate    As Field

On Error GoTo Error_Handler

If CheckIfFieldExist(TableName, "Late") = True Then
' Exist
Else
' Doesn't Exist

Set dbCurLate = CurrentDb()
Set tdfLate = dbCurLate.TableDefs(TableName)
Set fldLate = tdfLate.CreateField("Late", dbInteger)

tdfLate.Fields.Append fldLate
tdfLate.Fields.Refresh
End If

Error_Handler_Exit:
On Error Resume Next
Set fldLate = Nothing
Set tdfLate = Nothing
Set dbCurLate = Nothing
Exit Sub

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
Err.Number & vbCrLf & "Error Source: AddField2Tbl" & vbCrLf & "Error
Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Sub



2. Another method would be to simply add the field without testing and
simply trap the error that arises if the field already exists.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

03-Feb-10 02:54 PM

If you are trying to insert a new field in an Access table, what safeguards
do you have to ensure that the new field actually BELONGS in that table?
That is, if you want to get the best use of Access' relationally-oriented
features/functions, you need to "feed" it well-normalized data.  How are you
ensuring that the new field "fits" the table?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

03-Feb-10 03:18 PM

Thanks....but even with your mods its still doing exactly the same thing.
Trying to add the field whether its there or not (TRUE or FALSE) and hanging
on the error when it tries to add a field thats there already.
*pulling hair out*


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1

03-Feb-10 03:19 PM

Because the code only fires when it belongs in the table and isnt.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1

03-Feb-10 03:57 PM

Let me try stating my concern a different way...

How will your code prevent someone from adding a field to hold "My
Grandmother's Age" into a table of "Customers"?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

03-Feb-10 04:31 PM

That's very odd because I tried it at my end and it works fine for me.

What error is raise?
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

03-Feb-10 04:34 PM

Because the code only adds a field name of LATE to a specific table at a
specific request of a specific report, but only if its not there already.
They dont get to decide what table or what information only that they want to
see a report.  They arent writing code, they arent editing information, they
are not doing anything but selecting pre-ordained parameters to run a report.

All Im looking for is a second set of eyeballs hopefully picking up something
that I have done wrong in the code that Im not seeing because I have looked at it
all day, and so far it attempts to add Late if its not there and if it is
there, I just want it added if its not.  So can you help with the problem or
not?

Thanx
RHM



--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1

03-Feb-10 04:55 PM

3191


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1

03-Feb-10 04:58 PM

3191: Can not define define field more than once
Because its trying to add it even tho its already there.


--
Message posted via http://www.accessmonster.com

03-Feb-10 07:36 PM

Here is a different function that checks a different way.  It is probably a
bit slower, but not noticeably on most computers unless your were doing this
in a loop for some reason and checking a very large number of tables and fields.

Function CheckIfFieldExists(strTableName As String, _
strFieldName As String) As Boolean
Dim tDef As TableDef
Dim db As DAO.Database
Dim tfTableCheck As Boolean, tfFieldCheck As Boolean
Dim fldAny As Field

Set db = CurrentDb()
'Make sure the table exists
For Each tDef In db.TableDefs
If tDef.Name = strTableName Then
'Now check for the field
tfTableCheck = True
For Each fldAny In tDef.Fields
If fldAny.Name = strFieldName Then
tfFieldCheck = True
Exit For
End If
Exit For
Next fldAny
End If
Next tDef

If tfTableCheck = False Then
'MsgBox "No such table"
CheckIfFieldExists = False
Else
CheckIfFieldExists = tfFieldCheck
End If

End Function


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

03-Feb-10 07:56 PM

Thanks for the clarification.  I still do not understand the why well enough
to be able to help.  Hopefully one of the other newsgroup readers can help.

It sounds like you are using this approach to work out a report ... if so, it
is not necessary and is rarely a good idea to create a table in Access simply
to pull together the data for a report.  In most instances, you can use
queries.

I have been trying to learn enough about your business need (not the technique
you propose) to see if there might be other options available to you.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

04-Feb-10 09:46 AM

Changed to this and still getting error 3191.  So code still not recognizing
that field check = True.
Thanx for suggestion tho.


--
Message posted via http://www.accessmonster.com

04-Feb-10 09:47 AM

Yes definitely using approach to work out report.
How would you do it with a query?


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1

04-Feb-10 10:45 AM

The function works for me.  I tested it with a bad tablename and a bad field
name in an existing table and with valid table and field names.  It only
returned true in the last case.

You can test to see if the function is working by using the VBA immediate
window and entering
?CheckIfFieldExists("SomeTable","SomeField")
and varying the validity of the table and field names you are passing

I am going to guess that something may have corrupted your database or that
something in your calling function is wrong
If CheckIfFieldExists("tblCDRLMetricsFinal2", "Late")=True Then
' Exists
Else
...
End If

If corruption in the VBA code is the problem, you can try the following from
Allen Browne.

Here is a standard sequence to try to rescue a corrupted mdb

0. Make a backup copy of the file.
00. Make a backup copy of the file.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:   http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file.
Decompile the database by entering something like this at the command
prompt while  Access is not running. It is all one line, and includes the quotes:

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, and reference ambiguities are resolved.

If it is still a problem, the next step would be to get Access to rebuild the
database for you. Follow the steps for the first symptom in this article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

--
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html Reply to group,
rather than allenbrowne at mvps dot org.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

04-Feb-10 11:24 AM

How depends on what.  What data structure do you have?  Knowing that would
provide folks here a way to offer suggestions on using queries.

As a general approach, you may find it much more flexible (and much less
work) to use queries as your sources for your reports.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

05-Feb-10 10:54 PM

This is using ADO:

Private Function Check4Field(strTableName As String)

Dim fldName As Variant
Dim bFieldExists As Boolean
Dim rs As New ADODB.Recordset

rs.Open strTableName, CurrentProject.Connection

bFieldExists = False
For Each fldName In rs.Fields
'Debug.Print fldName.Name
If fldName.Name = "late" Then
bFieldExists = True
Exit For
End If
Next fldName
rs.Close
Set rs = Nothing

Check4Field = "Field 'Late' does not exist."
If bFieldExists Then Check4Field = "Field 'Late' exists."

End Function

08-Feb-10 07:07 PM

I always use queries as the source for my reports.  This one was special case
because it involved using several parameters that the user could apply to
data that was being used by a crosstab query.  It was just easier to create a
temporary table to hold the special request and then run the crosstab.  You
probably know how persnickety crosstabs are.  Anyway, it got it to work...and
its working wonderfully.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1

08-Feb-10 07:09 PM

Finally got it to work, i went with checking for the existance of the field
and trapping for the error.  Works like a charm.
Thanks for your time and your suggestions.
Have a Nice Day!
RHM


--
Message posted via http://www.accessmonster.com