Count Fields in a Table 2

[mach27]

This is about importing Excel data into Access table. All Excel data (or sheet) can be uploaded into Access table regardless of the number of fields Excel data has. The imported file will be stored in a table called "Dump_table."

There is a specif Excel file that should be used but there will be time that users may upload a different file. The risk in uploading any Excel data will not always satisfy the format that I needed. What I want to do is check if what was uploaded is the correct one to be used.

Questions:
1. How will I count the number of fields in a table using VBA?
2. How will I check if the specific field name is present to the recent upload (Dump_Table)?

Thanks in advance.

 

[dhookom]

If you have reference set to the Microsoft DAO library, you can get the field count like:

 Dim intFieldCount as Integer
 intFieldCount =currentdb.TableDefs("yourtable").Fields.Count

To check for a specific field, you could use the field and the Dump_Table in a DCount(). If this raises an error, then the field is missing.

You could also write code that loops through the fields collection of the tabledef using DAO code.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

In a standard code module create the following function:

Function FieldExists(strTableName As String, strFieldName As String) As Boolean
On Error Resume Next
FieldExists = (CurrentDb.TableDefs(strTableName).Fields(strFieldName).Name = strFieldName)
End Function

And now in your code:
If FieldExists("Dump_Table", "yourFieldName") Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mach27]

Thanks guys, each solution works.

/salute