We need to upload a small amount of additional records to a table from an Excel sheet. Is there a way to use the Access Import function to add the additional data to the table (truncate it). The table was created by uploading the same Excel sheet. But now, when records are added, we need to add them to the table. The tables are linked to SQL but I do not want to use an SSIS because there are only a few records and there must be a way to use Access functions. Suggestions please.
Add a comment
|
2 Answers
It may be easiest to link the excel sheet and run an append query to add data from Excel to existing table. Once linked, this can be done in the query design window.
1 Comment
user745778
I'm trying to avoid linking to an Excel sheet all the tables link to SQL tables. Anyway, I wrote an SSIS to upload the data. But one field is causing problems. The data is over 255 and I've tried converting it in SSIS data conversion but non seem to work. I'm posting a seperate question pertaining to this situation. Thanks
You did not specify versions of Excel or Access.
I did this with a test 2003 Excel sheet with cells containing 1000+ characters. An import in Access 2003 detects the data type as a memo field, which is correct, when there are that many characters, so it should work for you. It may be your Excel data has other ingredients causing an import issue. How is the excel data derived?
Have you tried importing to Access? It should work fine. If your ultimate target is another database why use Access as an intermediary?
I agree a linked table seems like a really simple method to update a table if you are using Access, but that is your choice.
