 | |
need some info about TransferSpreadsheet acImport
comp.databases.ms-access comp.databases.ms-access
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
need some info about TransferSpreadsheet acImport -
09-20-2011
, 02:52 PM
#2
| |||
| |||
|
|
DoCmd.TransferSpreadsheet acImport, is the acimport an append or an update or either or ? I need to import some data from excel but not sure exactly what import does. can it be used to append or update based on if the record exist? thanks for any help with this |
No update.
Insert (append) only.
You could easily have found this out for yourself using a test database and
spreadsheet.
You will need to import into a staging table and use queries to handle the
imported data after the import.
#3
| |||
| |||
|
|
sparks wrote: DoCmd.TransferSpreadsheet acImport, is the acimport an append or an update or either or ? I need to import some data from excel but not sure exactly what import does. can it be used to append or update based on if the record exist? thanks for any help with this Nope. No update. Insert (append) only. You could easily have found this out for yourself using a test database and spreadsheet. You will need to import into a staging table and use queries to handle the imported data after the import. |
#4
| |||
| |||
|
|
I did test it and all I can get it to do is append. |
Quote:
|
I did not know if there was some way to get it to update if the record existed or not. It seems not. Is there any type of import command that will do an update? |
Import the data into a staging table and use queries to handle the inserts
and updates. (same answer as my original reply). If you want more details,
we need more details (table structure, key fields, ...).
FWIW, the answer would be the same for most databases, not just Access. For
SQL Server Integration Services, there is a Merge transformation, but in its
original 2005 form, it was slow and inefficient, and most of us wound up
writing our own procedures (which usually involved staging tables). I
understand they've improved it in SQL 2008, but I haven't had a chance to
play with it.
#5
| |||
| |||
|
|
sparks wrote: I did test it and all I can get it to do is append. So you had your answer ... I did not know if there was some way to get it to update if the record existed or not. It seems not. Is there any type of import command that will do an update? My answer has not changed. No. Import the data into a staging table and use queries to handle the inserts and updates. (same answer as my original reply). If you want more details, we need more details (table structure, key fields, ...). FWIW, the answer would be the same for most databases, not just Access. For SQL Server Integration Services, there is a Merge transformation, but in its original 2005 form, it was slow and inefficient, and most of us wound up writing our own procedures (which usually involved staging tables). I understand they've improved it in SQL 2008, but I haven't had a chance to play with it. |
#6
| |||
| |||
|
|
have no problems using this as an append. It works great. I will make a blank table with the same variables and write them there for the update queries. I was lucky that I made the variable names in the excel file and the database the same. That is what made this very easy for the append part. On Wed, 21 Sep 2011 09:54:29 -0400, "Bob Barrows" reb01501 (AT) NOyahooSPAM (DOT) com> wrote: sparks wrote: I did test it and all I can get it to do is append. So you had your answer ... I did not know if there was some way to get it to update if the record existed or not. It seems not. Is there any type of import command that will do an update? My answer has not changed. No. Import the data into a staging table and use queries to handle the inserts and updates. (same answer as my original reply). If you want more details, we need more details (table structure, key fields, ...). FWIW, the answer would be the same for most databases, not just Access. For SQL Server Integration Services, there is a Merge transformation, but in its original 2005 form, it was slow and inefficient, and most of us wound up writing our own procedures (which usually involved staging tables). I understand they've improved it in SQL 2008, but I haven't had a chance to play with it. |
import to fail is someone entering a text into a numeric or date column;
which if very likely with a spreadsheet.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.