Hi fellows, I am working on a material management system. There is a table which only includes material codes and descriptions as a primary key and there is another table for data entries connected to the material list table with one-to-many relation ship. On data entry table users are importing packing lists from excel with transferspreadsheet macro. If material is not in the packing list user suppose to add this material to the mat. list table. But its very likely to miss those items which are in the packing list and are not in the mat list table.In such a case access will import packing list without these items. Therefore I would like to put a control mechanism which gives a warning to the user if there is additional material which was not defined in the mat. list before so user can add it.
Data check while importing from Excel
- Thread starter Desperate
- Start date
- Local time
- Today, 04:09
- Joined
- Sep 12, 2006
- Messages
- 16,055
the best way to do things like this is to import first to a temporary table.
you can either create a new table, or import to an existing table (clearing it down first). The latter way is often better, as you can manage indexes easier. (I am not 100% sure if you can import to an existing table with transfer spreadsheet, without checking - you can definitley import a csv in this manner)
THEN you can do any validation checks on the import, BEFORE you decide to process it - or indeed you can reject it. At this point you can write append/update queries to modify the control tables you are defining
NOTE that importing a spreadsheet (docmd.transferspreadsheet) is actually less reliable than importing a csv(docmd.transfertext), as with a spreadsheet, access will often format fields wrongly, and in general you get less control.
With a bit of work, you can build a very useful automatic data import routine.
you can either create a new table, or import to an existing table (clearing it down first). The latter way is often better, as you can manage indexes easier. (I am not 100% sure if you can import to an existing table with transfer spreadsheet, without checking - you can definitley import a csv in this manner)
THEN you can do any validation checks on the import, BEFORE you decide to process it - or indeed you can reject it. At this point you can write append/update queries to modify the control tables you are defining
NOTE that importing a spreadsheet (docmd.transferspreadsheet) is actually less reliable than importing a csv(docmd.transfertext), as with a spreadsheet, access will often format fields wrongly, and in general you get less control.
With a bit of work, you can build a very useful automatic data import routine.
Gemma thanks for your reply. I don't have any problem with importing excel. It imports data to a temporary table and from table user can save data to the main table with query. Till this point every thing is okay. The problem is while importing the data from excel.If there is a new material which was not entered in material list table, it will not be imported. Lets say there are 100 rows to be imported from excel and each row is one material. If 2 of these materials are not on material list table, access will import only 98 rows and user will save this without noticing the 2 missing items. On that point user should get a warning that he should add 2 mising items to the mat list.
