Access 07: How to pull in information

[HopyMSU]

Hi all - I'm pretty much an access/db newbie, so I would love some
help and opinions here. Basically, I need to know how to use Access
most efficiently (or if I should use it at all!).

I work with a client that sends me a daily feed of information in an
excel file. Most of the information is text and date based. I do a lot
of data entry (adding to) and 'drilling' into the information they
send and I need to create reports/exports from this information.
That's why I use Access instead of Excel (am I wrong to do that?).
Each day I need to:

-Append any new records they have added to the cumulative excel file
they send
-Add on information in values (excel cells) that they have added on to
or created (no new fields have been created, however) to the excel
file
-Identify new records and assign them an internal code
-Store internal information about each record that is hidden from the
client
-Create reports of new records to send to individuals within my
company
-Pull 'past due' records to send to individuals within my company
-Collect data from these individuals regarding the records that I send
them
-Create end of the month invoices for the client and for individuals
within my company
-At the end of each day, I need to export the main table, with updated
information, back into excel

I know that's a lot of think about and probably a broad question. So,
let me ask a few specifics:

1. Is Access the right tool for the job?
2. Concerning the client's cumulative excel file, how should I import
that data to an existing table? If I append, individual values don't
update for existing records; if I link I can't edit information (which
is the primary use of access for me!).
3. Is it even reasonable to expect the database to handle all of the
above?

Hope to hear from you all. Thanks!
Andy

 

[aaron.kempf]

no

you shouldn't use Access
you should use SQL Server 2000 Developer and learn DTS


it is 100 times more powerful and reliable than
DOcmd.TransferSpreadsheet

and while you're at it-- Access MDB is obsolete; and it has been for
10 years
use Access Data Projects unless you want to be stuck making $12/hour
for the rest of your life

SERIOUSLY

 

[Aaron Kempf]

get an ETL tool such as informatica or DTS

Access is for retards and 1st graders

 

[HopyMSU]

How helpful. Does anyone have anything of value to add or is this all
that I'm going to get?

 

[Ken Snell \(MVP\)]

Initial comments regarding your questions:

1. Is Access the right tool for the job?

ACCESS is fully capable of doing what you seek, but it will take some
programming of a database to realize all of the desired outcomes.

2. Concerning the client's cumulative excel file, how should
I import that data to an existing table? If I append, individual
values don't update for existing records; if I link I can't edit
information (which is the primary use of access for me!).

Typically, it's best to import data to a "temporary" table (usually done via
a TransferSpreadsheet action in VBA code or in macro), and then use an
append query to copy the desired records into the permanent table (and then
delete the records from the temporary table so that it's ready for the next
import). This lets you work with the data to eliminate duplicates, clean up
/ change the data, etc. before you add the records to the permanent table.
In order to determine when "new" records have been added to the EXCEL file,
you'll need some way to "identify" when a data record is new or old, as well
as duplicate vs. original. Data structure becomes very critical.
Essentially, if you can figure out how to do the data identification work
"manually", it's likely that you can program ACCESS to do it
"automatically".

3. Is it even reasonable to expect the database to
handle all of the above?

Yes. The "key" to success will start with paper and pencil - identifying
what you need to store, what you have as initial (EXCEL) data, how you
identify the data that you want and don't want, etc. From there, you can
begin work on designing a database to do what you seek. Caution: ACCESS
takes a lot more time and effort to learn than EXCEL, so things will go
slowly at first. Time spent in designing on paper what you want will more
than pay off in time spent "fixing" problems with data structures, etc.