Checking for for in Access using VB code

[33216CLC]

I am writing an upgrade to a program and would like to write a program to check whether certain fields exist in a table. What method could I use to check for this? I understand how to write code to add or alter a table, but How can I check for the existence of a field?

Thanks,

Cooleen [sig][/sig]

 

[Francis]

Function FieldExists(DBname$, tblname$, fieldname$) As Boolean
'*********************
'find if a field exists in a table
'**********************

Dim db As Database, tbldef As TableDef, field As field
On Error GoTo FieldExistsErr
Set db = OpenDatabase(DBname)
Set tbldef = db.TableDefs(tblname$)
Set field = tbldef.Fields(fieldname$)
FieldExists = True
FieldExistsEXIT:
On Error Resume Next
Set field = Nothing
Set tbldef = Nothing
db.Close
Set db = Nothing
Exit Function
FieldExistsErr:
GoTo FieldExistsEXIT
End Function [sig][/sig]

 

[MichaelRed]

Coolene,

The function exists provided by Francis is an approach to check for a specific field in a specific table in a specific database.

Use a similar approach, but instead of checking the existance of a single field, itterate through the fields tables/fields collection and "log" the table and field names to a table of your own creation. Then, write a simple query to find the exact (or close to/"Like&quot fields in all of the tables in the database.

If the database in question is an Ms. Accesss db, AND you have Ms. Access, there are MSys* table in the data base (normally hidden) which can provide the information more directly.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[33216CLC]

Thank you very much Francis and MichaelRed. Will do

Cooleen

[sig][/sig]

 

[33216CLC]

Hi Michael,

In your post you mentioned using a similar approach to check for a multitude field names. I am on clear on ho I could go about this. Could you please expound a bit more on the second paragraph of your post? I have almost 100 fields that I need to check for.


Thanks,

Cooleen

 

[swilliams]

Dim db as Database
Dim td as TableDef
Dim fld as Field

Set db = Workspaces(0).OpenDatabase(&quot;Database_Path&quot;, False, False)
For Each td In db.TableDefs
For Each fld In td.Fields
' do something checking fld.Name eg:
MsgBox &quot;Field &quot; & fld.Name
Next
Next

Simon

 

[MichaelRed]

Adding some small pieces to Simon's code,

Public Function basListFields(Database_Path) As Boolean

    Dim db As Database
    Dim dbLocal As Database
    Dim rstFields As Recordset
    Dim td As TableDef
    Dim qdf As QueryDef
    Dim fld As Field

    Dim strSql As String

    Set db = Workspaces(0).OpenDatabase(Database_Path, False, False)

    Set dbLocal = CurrentDb
    Set rstFields = dbLocal.OpenRecordset("tblFields", dbOpenDynaset)

    strSql = "Delete tblFileds.* from tblFields;"
    Set qdf = dbLocal.CreateQueryDef("", strSql)
    qdf.Execute

    For Each td In db.TableDefs
        For Each fld In td.Fields
            With rstFields
                .AddNew
                    !dbName = Database_Path
                    !FldName = fld.Name
                    !tblName = td.Name
                    !dtFound = Now()
                .Update
            End With
        Next
    Next

End Function

This needs a table (tblFields) in the database where this code resides with the fields in the {.addNew} : {.Update} block of code. It will &quot;log&quot; all of hte fields of all of the tables in the target database (argument &quot;Database_Path&quot, which must be the fully qualified path to the database. You may add additional fields to tblFields to include the individual field properties (type and Length).

This function clears the (local) table [tblFields] on each execution, so if you need to do multiple databases, you need to modify it to do what you want here, or just copy the table to a new name for each execution.

I would probably generate an additional table with the table and field names I was verifying (probably a one time manual data entry job), and do an 'unmatched ' query on the table & field names between the two.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[33216CLC]

Thanks alot, guys.

Cooleen