Deleting/removing FIELD from a table

[VegaLA]

Good day all,
weird one this and having searched the forums
and looked into the 'DeleteObject' method i'm surprised that method does not allow you to delete a field from a table on the fly.
I have a process that allows users to import data from a spreadsheet to the DB, then add to that Data as more Daat in the form of a sheet comes in. However sometimes the odd field F8, FWhatever sneaks in and it throws it off. i have managed to trap this and flag it but cannot find any syntax that will remove this field from the Import table before moving onto the next step. I dont want to halt the procedure and manually remove the bogus field as that defeats the purpose of my automating this process, nor do I want to specifically code the correct fields for the next step, ideally I just wnat to delete/remove the offending field before going further.

So, is removing a field from a table possible in VBA? If so please let me know how.

Thanks in advance,
Mitch

 

[SOS]

Just import to a staging table and then run an append query to add the data to the real table which is linked to the form.

 

[VegaLA]

Just import to a staging table and then run an append query to add the data to the real table which is linked to the form.

Thanks for that. Ideally I wanted to avoid that but if it is not possible to remove the offending Field i'll have to go that route.

Thanks again.

 

[SOS]

Thanks for that. Ideally I wanted to avoid that but if it is not possible to remove the offending Field i'll have to go that route.

Thanks again.

It is possible to delete a field but it will also cause problems, for example if someone else has their frontend open then you can't do the operation because the table is locked, etc.

But you can use SQL to drop a column:

Function DropMyTable(strTableName As String, strColumnName As String)
Dim strSQL As String
 
strSQL = "ALTER TABLE " & strTableName & " DROP COLUMN " & strColumnName
 
CurrentDb.Execute strSQL, dbFailOnError
End Function

 

[ghudson]

You can delete the extraneous fields in the excel file before you import the file. Excel remembers if the user did anything to a cell [even if they delete the cells contents] and will give you the extra fields when you import the data. As SOS mentioned, it is best to import into a temp table to do some validation checks before you append/update the data into your table. Search around if you want to find some code to manipulate an Excel file from Access.

 

[VegaLA]

Cheers for that.
Sorry for the delay in replying, finally found soem breathing time.

Thanks again!