MS Access and Flat file data?

Question

One company-A has MS access database for their website.
And the company-A needs to update their product table according to a flat file data from other company-B.

The flat file data is updated by other company-B’s FTP server daily.
We need to download the flat file data and convert it to appropriate data format and update company-A’s product data table according to new data.

Do you think it is possible to be done by automatically without human interaction?
If it’s possible, Do you think this can be done within Company-A’s server, using server script language, such as ASP.net?

Sincerely
Jin

Answer 1

Yes, this can be automated, but there are several things you should be careful to consider. If you use Microsoft technologies like ASP.net it is very easy to add data to an Access database using built-in Windows APIs. Consider using Visual Basic also.

Company A should be very careful to make sure this project does not allow Company B to hack or hurt their server by sending bad data. SQL Injection or Buffer Overflow attacks are well known, but there are others.

This script/program/etc. should make sure the data from Company B matches your expectations, and should email you if the data is different from what you expect and then stop - otherwise the Company A database data might be messed up.

Also, make sure you have a way to limit how much data you download. If it is too much, it could fill up the hard drive on Company A's server, causing other programs on the server to fail.

For projects like this, you should also follow good software development processes : the "source code" or script program should be stored in a Version Control repository like CVS or Subversion, and there should be a document describing what it does, who wrote it, how to use it, etc.

Choosing which programming or scripting language to use depends on many things. If someone already knows Perl, why use ASP.net to work with a text file? But that might not make sense if you do not have Perl on the server and it is hard to get additional software installed on the server because it must be done by an administrator.

Answer 2

Your problem is a disaster in the making. verify you're making backups primary. purchase various thumb drives and positioned them someplace risk-free. next, convert your information into get entry to. with a bit of luck it relatively is is a flat report format. in case you may run autofilter or create a pivottable out of your information, then its a flat report. Open get entry to, create a sparkling database, bypass to table and import the whole excel table. Create large quickly queries from there. examine a primer on get entry to or examine up Google communities on MS get entry to.