Check if the field exists? 2

[DanKay1]

I want to insert a field into the table, but before doing it I want to check if it exists.
Is it possible to check if the table contains certain field name?

 

[PHV]

Why not simply put an On Error Resume Next instruction just before the DoCmd.RunSQL "ALTER TABLE ..." ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bubba100]

The following will look at your table and then look a specific field and return the name. The "8" is the location you will need to adjust to your needs. You then could use something like If strName<> "MyFieldName" then ...


Dim strTable As String
Dim strName As String
strTable = "YourTable"
strName = dB.TableDefs(strTable).Fields(8).Name

 

[DanKay1]

What if the location will change all the time? Is there a better way to do that check, maybe using ADO?

Thanks for your help so far. I might go with it if i am not going to find better solutions.

 

[hwkranger]

Public Function fFindField(strTableName, strFieldName As String) As Boolean
Dim rst As New ADODB.Recordset
rst.Open CurrentProject.Connection.OpenSchema(adSchemaColumns, Array(Empty, Empty, strTableName, strFieldName))
fFindField = Not rst.EOF
End Function

use by:

Call fFindField("YourTableName", "YourFieldName")

Randall Vollen
National City Bank Corp.

 

[hwkranger]

Oops,

I made a mistake, on how to use it:

use by:

if (ffindfield("YourTableName", "yourFieldname") then
.....blah.. blah...
end if

Hehe.. i write something and I don't even know how to use it ;-) LoL Hope this helps.

Randall Vollen
National City Bank Corp.

 

[hwkranger]

um.. another mistake:

if (ffindfield("YourTableName", "yourFieldname")) then

or

if ffindfield("YourTableName", "yourFieldname") then


I forgot a paranthesis (or added too many, depends on how you look at it.)

Randall Vollen
National City Bank Corp.

 

[DanKay1]

Thanks Ill try all the solutions you guys provided.
Your contribution is greatly appreciated.