|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
My Access application imports a fairly large amount of its information from
Microsoft Excel. Two tables in my database will need to be updated with
large amounts of data from Excel, and data already in the tables cannot be
harmed. One of these tables may be updated fairly regularly, and the import
process must be quick, easy, and reliable.
Now, as with most databases, both of these tables contain unique ID's and
foreign keys to parent tables. The users who are inputting data for the
Excel files should not have to know this information, and in many cases,
cannot.
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"tblActualCost", strActualImportPath, True
DoCmd.TransferSpreadsheet TableName:="tblActualCost",
FileName:=strActualImportPath, HasFieldNames:=True
I have tried these commands, but I sometimes wind up with errors complaining
that the autonumber field was left null. Even worse, these errors are then
saved as seperate tables, something that I simply CANNOT have happening in my
application. After all, how are users supposed to be able to go in and
delete junk tables Access has created after I've locked them out of doing so
with an MDE?
What can I do?
Dustin
|
|
Dustin
It sounds like you might be trying to import Excel data directly into your
"permanent" Access tables. Since Access is a relational data base, and
Excel generally holds "flat" data (not its fault, that's how it's built), it
would be rare that you could directly place Excel-organized data in
well-normalized Access tables.
Instead, a common approach is to link to or import Excel data "as is", then
use a series of parsing queries to distribute the data to the proper tables.
It would be during this parsing process that you could handle the issues
with primary and foreign keys.
Regards
Jeff Boyce
Microsoft Office/Access MVP
"Dustin Ventin" <DustinVentin[ at ]discussions.microsoft.com> wrote in message
news:8E402499-BEB7-4B2B-91FB-3CF4A118F5D6[ at ]microsoft.com...
[Quoted Text] > My Access application imports a fairly large amount of its information
> from
> Microsoft Excel. Two tables in my database will need to be updated with
> large amounts of data from Excel, and data already in the tables cannot be
> harmed. One of these tables may be updated fairly regularly, and the
> import
> process must be quick, easy, and reliable.
>
> Now, as with most databases, both of these tables contain unique ID's and
> foreign keys to parent tables. The users who are inputting data for the
> Excel files should not have to know this information, and in many cases,
> cannot.
>
> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
> "tblActualCost", strActualImportPath, True
>
> DoCmd.TransferSpreadsheet TableName:="tblActualCost",
> FileName:=strActualImportPath, HasFieldNames:=True
>
> I have tried these commands, but I sometimes wind up with errors
> complaining
> that the autonumber field was left null. Even worse, these errors are
> then
> saved as seperate tables, something that I simply CANNOT have happening in
> my
> application. After all, how are users supposed to be able to go in and
> delete junk tables Access has created after I've locked them out of doing
> so
> with an MDE?
>
> What can I do?
>
> Dustin
>
>
>
|
|
Dustin
I may have mis-stated my previous response.
You certainly CAN stuff Excel data into an Access table.
But you won't usually get good use of Access' features and functions unless
you feed it well-normalized data.
Regards
Jeff Boyce
Microsoft Office/Access MVP
"Dustin Ventin" <DustinVentin[ at ]discussions.microsoft.com> wrote in message
news:8E402499-BEB7-4B2B-91FB-3CF4A118F5D6[ at ]microsoft.com...
[Quoted Text] > My Access application imports a fairly large amount of its information
> from
> Microsoft Excel. Two tables in my database will need to be updated with
> large amounts of data from Excel, and data already in the tables cannot be
> harmed. One of these tables may be updated fairly regularly, and the
> import
> process must be quick, easy, and reliable.
>
> Now, as with most databases, both of these tables contain unique ID's and
> foreign keys to parent tables. The users who are inputting data for the
> Excel files should not have to know this information, and in many cases,
> cannot.
>
> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
> "tblActualCost", strActualImportPath, True
>
> DoCmd.TransferSpreadsheet TableName:="tblActualCost",
> FileName:=strActualImportPath, HasFieldNames:=True
>
> I have tried these commands, but I sometimes wind up with errors
> complaining
> that the autonumber field was left null. Even worse, these errors are
> then
> saved as seperate tables, something that I simply CANNOT have happening in
> my
> application. After all, how are users supposed to be able to go in and
> delete junk tables Access has created after I've locked them out of doing
> so
> with an MDE?
>
> What can I do?
>
> Dustin
>
>
>
|
|
Hi Dustin,
[Quoted Text] >the import
>process must be quick, easy, and reliable.
For this to be the case, you have to be able to assure the quality of
the data that's being imported, and it's not easy to do that if you're
using Excel to collect data from users.
By itself, Excel offers no protection against user actions such as
entering a text value in a cell that should be numeric, or accidentally
entering a foreign key value that does not exist in the other table, and
so on. If Excel is to be the front end, you'll need a combination of
1) sophisticated data validation, cell locking and protection in Excel
2) a multi-stage process for importing the data into Access: e.g.
(a) import to a temporary table
(b) run queries to validate the data (e.g. identify illegal value)
and eitehr correct it or report it to the operator
(c) finally append only good data to the permanent table(s).
On Wed, 27 Sep 2006 14:27:01 -0700, Dustin Ventin
<DustinVentin[ at ]discussions.microsoft.com> wrote:
>My Access application imports a fairly large amount of its information from
>Microsoft Excel. Two tables in my database will need to be updated with
>large amounts of data from Excel, and data already in the tables cannot be
>harmed. One of these tables may be updated fairly regularly, and the import
>process must be quick, easy, and reliable.
>
>Now, as with most databases, both of these tables contain unique ID's and
>foreign keys to parent tables. The users who are inputting data for the
>Excel files should not have to know this information, and in many cases,
>cannot.
>
>DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
>"tblActualCost", strActualImportPath, True
>
>DoCmd.TransferSpreadsheet TableName:="tblActualCost",
>FileName:=strActualImportPath, HasFieldNames:=True
>
>I have tried these commands, but I sometimes wind up with errors complaining
>that the autonumber field was left null. Even worse, these errors are then
>saved as seperate tables, something that I simply CANNOT have happening in my
>application. After all, how are users supposed to be able to go in and
>delete junk tables Access has created after I've locked them out of doing so
>with an MDE?
>
>What can I do?
>
>Dustin
>
>
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
|
|
|