Update/insert records to table from file

  • Thread starter Thread starter Frank M.
  • Start date Start date
F

Frank M.

Everyday I receive a datafile with changed and new
records that has to go into a table in an Access database.
Can I create two queries to update and insert new records
from the datafile to the table in Access?
If for example the table is "Customer" with
fields "CustID", "Name" and the linked datafile is
called "Custdata.csv" (with the same fields), how would
the two queries look?

If there are any errors during the insert and update will
they be put into new tables that can be accessed
afterwards?

Finally, can the queries be called from VBA, so that they
could be executed at regular intervals if the datafile
exists?

Help with this will be much appreciated.


Regards,


Frank M.
 
Hi,


I suggest you work with tables. CSV files can transfer data, but can hardly
be safe as far as data integrity is concerned. So, in the first step, I
would transfect the data from the csv file into an empty table. There, is
there were errors, I would have probably seen them (such as a wrong data
value, 11:65 for hour:minute, or an unknown employee (invalid ID), etc.).

Once the data is validated in the database, it is a matter to make an
append-update query. The typical example is a list of unit price, in
Inventory, to be updated by NewArrival:


UPDATE Inventory RIGHT JOIN NewArrival
ON Inventory.ItemID = NewArrival.ItemID
SET Inventory.ItemID=NewArrival.ItemID,
Inventory.UnitPrice= NewArrival.UnitPrice



Hoping it may help,
Vanderghast, Access MVP
 
Hi,

Thanks for the reply which definitely helps a lot. Now,
just a little clarification:

In the update query:

UPDATE Inventory RIGHT JOIN NewArrival
ON Inventory.ItemID = NewArrival.ItemID
SET Inventory.ItemID=NewArrival.ItemID,
Inventory.UnitPrice= NewArrival.UnitPrice

can the first part of the SET statement
(Inventory.ItemID=NewArrival.ItemID) be omitted as the
ItemID will be identical because of the join above, or
does it have to be there?

Also how will I handle new records? The above query
will ,as I understand it, only handle changed items. I am
thinking that there is perhaps a way to build a query
that finds the record in NewArrival which are not in
Inventory and then add those to Inventory, I'm just not
sure how.

Finally, can refer me to any webpage or book which
explains the more complicated queries in a good way.


Again, thanks a lot.


Regards,

Frank M.
 
Hi,


Embedded answers.


Frank M. said:
Hi,

Thanks for the reply which definitely helps a lot. Now,
just a little clarification:

In the update query:

UPDATE Inventory RIGHT JOIN NewArrival
ON Inventory.ItemID = NewArrival.ItemID
SET Inventory.ItemID=NewArrival.ItemID,
Inventory.UnitPrice= NewArrival.UnitPrice

can the first part of the SET statement
(Inventory.ItemID=NewArrival.ItemID) be omitted as the
ItemID will be identical because of the join above, or
does it have to be there?
Click to expand...


It has to. That's the whole trick, for a new
record. Since Inventory is the unpreserved side,
for new records, its "default" value is NULL,
because ItemID is not present YET in that table.
We have to update that NULL to the new
value of ItemID.

Also how will I handle new records? The above query
will ,as I understand it, only handle changed items. I am
thinking that there is perhaps a way to build a query
that finds the record in NewArrival which are not in
Inventory and then add those to Inventory, I'm just not
sure how.
Click to expand...


No need. That would be the case for an inner
join. Here, we used an outer join. The whole list
of modifications is "preserved". The new
records in table inventory have no match
in the old inventory, so the old inventory
supply a sequence of NULL in rows of
newly added items. (Because it is the
unpreserved side; the preserved side is
the list of new stuff). The UPDATE job, in
that case, is to change those NULLs to
new values. That works only under JET,
MS SQL Server does not allow update
on the unpreserved side.


Finally, can refer me to any webpage or book which
explains the more complicated queries in a good way.
Click to expand...


John L. Viescas has written many goods books,
take a look at "SQL queries for mere mortals" at
Addison Wesley, for example, at your book store,
open the book and read a few pages to see if it
fits your needs.

Otherwise, here seems also to be a good
interractive tool :-)



Again, thanks a lot.


Regards,

Frank M.
Click to expand...

your are welcome,
Vanderghast, Access MVP
 
Ok, got it!

-----Original Message-----
Hi,


Embedded answers.





It has to. That's the whole trick, for a new
record. Since Inventory is the unpreserved side,
for new records, its "default" value is NULL,
because ItemID is not present YET in that table.
We have to update that NULL to the new
value of ItemID.




No need. That would be the case for an inner
join. Here, we used an outer join. The whole list
of modifications is "preserved". The new
records in table inventory have no match
in the old inventory, so the old inventory
supply a sequence of NULL in rows of
newly added items. (Because it is the
unpreserved side; the preserved side is
the list of new stuff). The UPDATE job, in
that case, is to change those NULLs to
new values. That works only under JET,
MS SQL Server does not allow update
on the unpreserved side.





John L. Viescas has written many goods books,
take a look at "SQL queries for mere mortals" at
Addison Wesley, for example, at your book store,
open the book and read a few pages to see if it
fits your needs.

Otherwise, here seems also to be a good
interractive tool :-)





your are welcome,
Vanderghast, Access MVP


.
Click to expand...
 
Back
Top