Okay, I did not fully answer the question. There are a couple of methods you
could use to do this.
Create a table that has the same structure as your production table. We can
call it the import table. Then when you need to import the data, follow these
steps:
1. Run a Delete query that will delete all rows in the import table
2. Do the TransferSpreadsheet into the temporary table.
3. Run an Update query on the import that will put the information in the
field that identifes its origin. This query would include the method I
described using the calculated field.
4, Run an Append query to append the records from the import table to the
production table.
The other method differs only slightly. Instead of importing into an import
Access table, you can link to the spreadsheet using TransferSpreadsheet with
the acLink option. So the prodcedure would be
1. Run a TransferSpreadsheet using acLink to link to the spreadsheet.
2. Run an Append query to append the records from the linked table to the
production table. This query would include the method I described using the
calculated field.
3. Delete the link to the spreadsheet.
The second method does have one advantage. It will not cause bloat in the
Access Database. The first method will because when you delete records, they
are not really gone. They remain in the database until you do a compact and
repair.
--
Dave Hargis, Microsoft Access MVP
Andy Ralph said:
Thank you.
I know I am being dumb but how do I use this query in the import process?
I am using the following VBA code for the transfer:
DoCmd.TransferSpreadsheet acImport, 8, "tblSUGGSReq", tbFile, True, ""
where tblSUGGSReq is the Access table to be imported to and tbFile holds the
full path and file name of the file to be imported.
Sorry to be so dense.
Klatuu said:
Actually, you would do this in your import query. Create a calculated field
that references the control on your form that has the value you want in the
field:
In design view it would be like:
SheetRef: Forms!MyFormName!SCEnquiryRef
In SQL view it will look like: SELECT Forms!MyFormName!SCEnquiryRef AS
SheetRef
--
Dave Hargis, Microsoft Access MVP
Andy Ralph said:
Hope someone can help me...
I need to import data from an Excel template into my Access 2003 database.
So far so good...
As there can be many spreadsheets imported into the same Access table, I
need to generate a reference field for each line imported that identifies the
original spreadsheet it came from.
I have a Form which has the reference I want to use in a control (called
[SCEnquiryRef]) and a button which runs the VBA code "On Click". Is there a
simple way to use TransferSpreadsheet and also add the value of
[SCEnquiryRef] to each line in VBA?
Thanks in anticipation...
