Advatages of database over text file storage

[hmm]

Presently, my company has a material analysis system that records
time-stamped data entries in text files. Each data entry contains about
twenty different fields, all numbers. A new text file is generated every
time the system is stopped and restarted; each file contains from a few to a
few hundred entries. These files are transferred on a regular basis from the
field to our office computer over FTP. To do analysis, I must import and
gather all of the text files into an Excel file, which presently contains
about 9000 entries.

I would like to convince people here of switching over to database storage;
I have a few questions about its possible advantages over the method
described above:

1. How does disk storage space for an Access file compare with a comparable
text-file-based storage described above (not sure about all the text files,
but the Excel file used to store raw data presently occupies 3.1 MB. A
seperate file that extracts, averages, analyzes and summarizes the data
occupies 14.6 MB).

2. Same question regarding ease of importing data into Excel. Is there a
learning curve for doing this with Access?

3. How about transfer time, say over FTP? Can I transfer only new records
in the DB file, or would the whole file need to be re-transmitted?

4. Any other comments or suggestions would be appreciated.

Thanks.

 

[Klatuu]

Answers in line below
--
Dave Hargis, Microsoft Access MVP

Presently, my company has a material analysis system that records
time-stamped data entries in text files. Each data entry contains about
twenty different fields, all numbers. A new text file is generated every
time the system is stopped and restarted; each file contains from a few to a
few hundred entries. These files are transferred on a regular basis from the
field to our office computer over FTP. To do analysis, I must import and
gather all of the text files into an Excel file, which presently contains
about 9000 entries.

I would like to convince people here of switching over to database storage;
I have a few questions about its possible advantages over the method
described above:

1. How does disk storage space for an Access file compare with a comparable
text-file-based storage described above (not sure about all the text files,
but the Excel file used to store raw data presently occupies 3.1 MB. A
seperate file that extracts, averages, analyzes and summarizes the data
occupies 14.6 MB).

A totally irrelevant question. There would be an increase in disk space
usage, but with the capacity of drives today, making a decision on disk space
is not the way to go. It is not, however, a bad idea to know what your usage
is, but I don't have a way of estimating the difference for you.

2. Same question regarding ease of importing data into Excel. Is there a
learning curve for doing this with Access?

It is no more or less difficult than Excel. With Access, you can set up
what is known as in import specification. It can be used to both import and
export text files in a specific format. It is used with the TransferText
method/action. The difference between a Method and an Action in Access is
that a method is used with VBA and an Action is used with a macro.

Once set up, the import can be automatic. It can, in fact, be so automatic
it is done at specific intervals or at specific times without any human
intervention.

3. How about transfer time, say over FTP? Can I transfer only new records
in the DB file, or would the whole file need to be re-transmitted?

You can transfer one record or all of them. It depends on your business
rules. Typically, you would use a query with the filtering criteria you need
to define which data to transmit. If the rule is "transmit everything not
alread transmitted", then you would also want to use an update query to
timestamp or somehow identify the records has having been sent.
As to ftp, you need to use an ftp utility of some kind to do the transfer.
If you go to this site:

http://www.mvps.org/access/resources/downloads.htm

and scroll down to Internet Data Transfer Library, you will find a utility
there. I use it to downland some zip files in one of my apps that runs from
the Windows task scheduler every day.

 

[Steve]

From what you describe, everything could be easily done in Access. The field
would continue to transfer files to your office computer just as they are
now. That would be good because there would be no changes in the field and
the system they use. Access would take over and automatically do everything
including the material analysis with a click of a button or two. I am
suggesting you do the analysis in Access rather than Excel and eliminate
importing and gathering all of the text files into an Excel file. Access
could automatically import the data in the text files into appropriate
Access database tables.

To answer your questions .....
1. Access2000, AccessXP and Access2003 standalone databases have a max
size of 2 Gig. Databases can be split into frontends (functionality part of
the database) and backends (data tables). With multiple backends, you could
achieve virtually storage up to available space on your harddrive. The MB
files you mention are peanuts to Access!
2. It is easy to import data into Access if you know how to work with
Access; however, there is a very steep learning curve to automate your
system if you do not know how to work with Access. The learning curve starts
with properly designing the table structure you need.
3. You would FTP from the field just as you are now sending a text file
to your office. Access would then only import into its tables appropriate
data. This would be automatic and done through one or more queries.
4. Access has a diverisfied object model with an abundance of properties
and methods combined with a rich programming language that makes automating
what you are doing easy.

Your questions imply that you have little or no experience with Access.
Automating your system will be complex thus taking much time (and
frustration) on your part to get it up and running. I provide help with
Access, Excel and Word applications for a reasonable fee. I could create the
Access application to automate your system for you for a very reasonable
fee. You would save your time in learning Access and creating the
application. If you are interested, contact me at (e-mail address removed) and I will
be happy to give you a quote of my fees.

Steve

 

[Klatuu]

Ignore Steve. He is advertising his services again which is not allowed in
these newgroups, but he will not behave.
In addition, his qualifications, based on the few answers he has provided,
are suspect.

 

[John... Visio MVP]

I provide help with Access, Excel and Word applications for a reasonable
fee. I could create the Access application to automate your system for you
for a very reasonable fee. You would save your time in learning Access and
creating the application. If you are interested, contact me at
(e-mail address removed) and I will be happy to give you a quote of my fees.

Steve

Still do not have the intelligence to respect the users of these newsgroups?

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified people who willing provide free support.

Master stevie is not one of the qualified. He has proved many times how
incompetent he is and his only interest is scrounging for money.

Shall we ask Roberta if she approves of your sleazy behaviour?

John... Visio MVP

 

[Klatuu]

Ya know,
If every time I walked into a room and said something stupid I got the c*p
kicked out of me and was totally embarrased in front of the people in the
room, I think I would get the hint and stay out of the room and keep my mouth
shut.

But, then, that's just me.

 

[Steve]

Are you representing what MVP stands for with this response? Can you offer
proof of any thing you said?

Steve

 

[Klatuu]

I have nothing to prove, Steve. I am only trying to protect the unsuspecting.
My comments as to the appropriateness of your solicitation is based on the
following:

http://www.mvps.org/access/netiquette.htm

The following are specifically forbidden in the CDMA newsgroup:

Advertising of any kind, even if the product is free, a demo, or otherwise.
You may answer a question with a link to a commercial site which pertains to
the question. You may also add a phrase and/or link in your signature.

And

http://www.microsoft.com/info/cpyright.mspx#EPC

....Advertise or offer to sell or buy any goods or services for any business
purpose, unless such Communication Services specifically allows such messages.


As to your competency, my comment is based on the the content of posts where
you have attempted to answer questions.

 

[Steve]

You certainly do have something to prove ...

You attach the credentials of MVP to the end of your name so you have the
responsibility to demonstrate the standards of what MVP stands for.
Microsoft expects MVPs to exhibit courtesy, professionalism and abide by the
MVP code of conduct. MVP Rules of Conduct specifically state MVPs should at
all times avoid personal attacks and slurs in their interactions. Your post
is nothing but a personnal attack against me. You post is neither courteous
or professional. Your post is not truthful.

As for advertising, I responded to the OP with information that would
hopefully help him to move from Excel to Access. At the end I offered my
help at a reasonable fee if he was interested. If you call this
"advertising" so be it. But note that the intention of the content your
quote is to discourage advertising of products such as watches that have no
connection with Microsoft Access. If you are so intent on attacking me, why
don't you ever respond to advertisements of watches and such?

As to my competency, can you offer irreputable proof to back up what you
said. I don't mean a single example. You have made errors in some responses
and so have such stalwarts as Douglas Steele and Tony Toews.

Steve

 

[John... Visio MVP]

You certainly do have something to prove ...

Steve

Basically steve, you are a idiot. It is a simple matter of looking at some
of your past posts to see that you really do not understand what users are
asking and how to provid ethe appropriate help.

Dave has nothing to prove. He was given an MVP award because he proved that
he knows Access and is here to HELP users. You on the other hand are only
here to HELP yourself.

John... Visio MVP

 

[Steve]

Now here is something for you to prove ....

You attach the credentials of MVP to the end of your name so you implicity
state to all that you subscribe and live by the standards of what MVP stands
for. Now prove it .....

Do you endorse John Marshall's personal attack upon me or do you rebuke what
he is doing?

Steve

 

[John... Visio MVP]

Now here is something for you to prove ....

You attach the credentials of MVP to the end of your name so you implicity
state to all that you subscribe and live by the standards of what MVP
stands for. Now prove it .....

Do you endorse John Marshall's personal attack upon me or do you rebuke
what he is doing?

Steve

You lost any personal credibility when you started harrassing posters for
work. If you were any good, you should have enough repeat business so that
you would not need to troll the newsgroups for work.

Your type of "help" is not needed, just go away.

John.. Visio MVP

 

[Steve]

So you are not man enough to speak for yourself!! You have to have the likes
of John Marshall who is a disgrace to what MVP stands for speak for you.
This in itself shows YOUR disregar for the standards of conduct an MVP is
expected to adhere to.

Steve

 

[mcnews]

Presently, my company has a material analysis system that records
time-stamped data entries in text files. Each data entry contains about
twenty different fields, all numbers. A new text file is generated every
time the system is stopped and restarted; each file contains from a few to a
few hundred entries. These files are transferred on a regular basis from the
field to our office computer over FTP. To do analysis, I must import and
gather all of the text files into an Excel file, which presently contains
about 9000 entries.

I would like to convince people here of switching over to database storage;
I have a few questions about its possible advantages over the method
described above:

1. How does disk storage space for an Access file compare with a comparable
text-file-based storage described above (not sure about all the text files,
but the Excel file used to store raw data presently occupies 3.1 MB. A
seperate file that extracts, averages, analyzes and summarizes the data
occupies 14.6 MB).

2. Same question regarding ease of importing data into Excel. Is there a
learning curve for doing this with Access?

3. How about transfer time, say over FTP? Can I transfer only new records
in the DB file, or would the whole file need to be re-transmitted?

4. Any other comments or suggestions would be appreciated.

one advantage over using an excel file to manage all of your data is
that once in an access table your data becomes searchable. you can
create and store all kinds of queries. you can also create useful
reports.
also, sooner or later your spreadsheet will screw up on you. i've
seen it happen too many times when people try to use a spreadsheet
when they should be using a database.
you can keep your current processes in place then import the FTP'd
text files. if the text files a formatted the smae way each time then
importing is pretty much strait forward.