Search field names in a table and add if does not exist

[newbieX]

I have lots of excel spreadsheets (2003 format) that I am trying to automate loading into a master table. Some of the spreadsheets have all of the fields in them, and some only have a few of them. My thinking is that I need to import the spreadsheets into a table, standardize the format, and run the append query against it.



For instance:

I have a main table called “CensusData” that contains all the census data in a region. I get census data from several companies in Excel 2003 spreadsheets. Not all companies collect the same data. Some do full scale data collection; some just collect a few fields. The field (column) names are standardized but some spreadsheets may only contain 10 fields (columns); some may contain all 35, and some in between.

I am thinking the best approach (this need to be automated) is to import the spreadsheet into a new table, called “Import Table”. I have a table that contains all the fields possible in it called “Append Table. I will then create an Append Query where I append all the records from the Import Table to the Append Table. The problem is that if the field name does not exist, the append query does not work. So I am thinking I need to write a function that searches through the field names in the Import Table to see if it exists and if not, add it. I don’t care if it appends null data. It is a null value for that field anyway since it doesn’t exist in the original spreadsheet. That way, no matter what the original size of the spreadsheet was, by the time I append the data, the Import Table structure is the same after adding all the fields, and thus the append query will run.

Does this approach make since? If so, could someone help me write a function that searches through the field names in a table to see if it exists? If not, then add it using the following data types. Below is a very small sampling of the data fields I need to check. (I can figure out the other fields based on these samplings)

Length (double)
Width (double)
NOD (int)
DOB (date)
Zone(text,2)

Using Access 2007.

[June7]

Should not have to modify a table. Import or link to the spreadsheet then run code to construct SQL statement to append records. Consider:

Dim rs As DAO.Recordset
Dim i As Integer
Dim strFields As String
Set rs = CurrentDb.OpenRecordset("Sheet1")
For i = 0 To rs.Fields.Count - 1
strFields = strFieldsDest & "[" & rs.Fields(i).Name & "],"
Next
strFields = Left(strFieldsDest, Len(strFieldsDest) - 1)
CurrentDb.Execute "INSERT INTO Table1 (" & strFields & ") SELECT " & strFields & " FROM [Sheet1]"