Adding a new column in Access table and update

[aman]

Hi there

I am writing the following code to update Access table with data from excelsheet and It works fine if the columns in Access table are same as that of excelsheet. But in case excelsheet has one new column that is not present in Access table then how can we add a new column in Access table and update it using excelsheet data.

Dim xlpath As String

xlpath = "J:\" & Text40 & "-Sent.xls"
DoCmd.DeleteObject acTable, "excelMaster"
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel7, "excelMaster", xlpath, -1
strsql = "UPDATE excelMaster INNER JOIN tblcustomer ON excelMaster.ID = tblcustomer.ID SET tblcustomer.Grading = [excelMaster].[Grading],tblcustomer.[PAF Match Type]=[excelMaster].[PAF Match Type];"
DoCmd.SetWarnings False
DoCmd.RunSQL strsql
DoCmd.SetWarnings True
MsgBox "The Project File " & Text40 & " has been updated in the database"

Any help would be much appreciated.

Thanks

 

[JANR]

Are you really goning to let users modify your tables?? That opens up a can-of-worms, so don't.

Just a question, what holds the correct structure the excel sheet or your Access database?

If you really want to do it, look up in Access help, under the searchterm "ALTER TABLE"

JR

 

[vbaInet]

The answer is don't because you don't have a good reason to do so. If I was a user of your database I could create hundreds of new columns just to "bugger" your tables.