Is this possible??

[Shanin]

I have a co-worker that would like me to build a database that centralizes a
huge amount of data and am wondering if Access would even be the avenue to
try and do this in. My thoughts initially are this would be a huge
undertaking and would probably lead to a swollen database and would be better
served by something other than Access.

What they want is this, a database that they can click on a client's name,
where this would pull up a main screen which would show who worked for that
client, and several other options like med errors for this person, the
person's budget, the person's plan and even being able to click on the staff
that work for the client and being able to view their trainings, schedule,
etc. The main problem I see in all of this is all of this stuff is in
numerous different applications. All HR/staff trainings, employee
information is in an Access Database I already built. Schedules are in a
FoxPro database which also is our timekeeping system. I have our HR database
linked to that to pull schedules so that is not a problem. Budgets, errors,
etc, these are under the clients folder on a server and are in Excel and
Word, some are even still in Lotus. The part I would find hard is the budget
forms come from the State and have to be in that form and you have to hav a
new on every month, sometimes more than one if an amendment is made so you
would have a hard time trying to link to something where the file name would
be constantly changing.

Without a total re-work of the agency on how things are reported, I don't
see this as being possible and even then, I don't think Access would be the
best solution to store that much data.

Any thoughts?

 

[Jeff Boyce]

MS Access makes a great RAD tool. You can quickly prototype both data
structures and user-interfaces. And you can connect to a variety of data
sources (such as those you described).

However, if the underlying data sources are not well-normalized (internally
and/or between sources), you (and Access) will have to work extra hard to
get good use of Access' relationally-oriented features and functions.

If you decide you will be migrating the data from their current sources to a
new back-end/data store, you can use MS Access, or you can use something
like SQL-Server, and use Access as a front-end to connect to the data.

Without some idea of YOUR sense of "that much data", we can only guess
whether Access could handle it.

Without some idea of YOUR environment (PC-horsepower, network "pipes",
version of Access, number of simultaneous users, number of data entry users
vs. number of lookup users, ...), we can only guess whether Access could
handle it.

You did post in an MS Access newsgroup ... what kind of response were you
looking for?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Shanin]

I'll start with the hardware. We have 2 IBM Servers each with dual Xeon
3ghz. The desktops are all IBM 3ghz and we have 16 of those, Servers are
running 2000, desktops XP. Routers and switches I'm not sure of.

As for the data. All our timekeeping data is stored on one server, it runs
the telephone time-in, well actually a dedicated desktop does, but the data
is all stored on the server. This is the FoxPro database and that has all
employee schedules in it because that software uses the schedules to figure
overtime. All the client data is stored on the other server. Basic setup
there is on a shared drive, each client has their own folder, we have around
40 clients. Under each client's folder are sub-folders for specific types of
data (this is the same for all, meaning all clients have the same
sub-folders). They would have a plan folder, medical, etc. The plans, under
the plan folder, are done in Word and can be 30-50 pages long and can contain
graphics. These are done every year, so there will be one for every year
they have been with us. Also on the shared drive would be a folder called
program budgets, and under that there will be a seperate folder for each
year, and under that a folder for each client. They will have an Excel
spreadsheet for each month for their budget, so there will be 12 Excel
spreadsheets (possibly more if an amendment is done mid-month) under that.

That's a basic overview of where the data is that I'd have to pull in. I'm
guessing there wouldn't be more then 4 people needing to access this at a
time, but the maximum that would be in at a time would be 11.

I guess what I would want to know, for one, is more about the RAD tool. My
biggest concern though is how I'd be able to look up these Excel budget
sheets when a new one is made every month. Example, I want to click on Joe
Schmoe's name, click on his budget, and look up his August 2007, how will it
get this into Access easily without having to define each month a new file
location? Would you want to enter the data into Access and have it store it
and export it to an Excel form, or just set it up to keep the data where it
is and just have Access pull it in to view?

 

[Shanin]

Oh forgot to say, all computers have Access 2000 on them.

 

[Jeff Boyce]

I still don't have a sense of the "volume" of data ... does the total size
of all files currently being used exceed 100 Mbytes?

Since you can use Access to connect (i.e., "link") to data from FoxPro,
Excel, ..., you can use Access to prototype a way to mix/match your various
data sources. This is what I meant by using Access as a RAD (rapid
application development) tool.

Of course, all those disparate data sources would have to share common
fields/IDs/... before you could use Access to "join" them together (using
queries).

This strikes me as somewhat "bigger than a breadbox" ... how much time do
you have to get it done? Do you have any experience using Access? It has a
bit of a steep learning curve.

Do you have any experience with normalization and relational data design?
To make the best use of Access, you'll need these.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Shanin]

From a quick overview of the folders that would need to brought in, there is
over 5 gigabytes of information. Some of that probably wouldn't need to be
in, but even discarding that I would still bet over 2 gigabytes as of now,
which would be ever growing.

Sorry I was a bit slow on the lingo there, but I understand what your
saying. I do have experience in Access mostly from fooling around with it
myself and asking a question here if I get stumped. I'm familiar with
normalization and all. I've linked to Excel before and other databases, but
never have to Word as I'm kind of uncertain how to have a common field in a
text document unless you insert a table with it. I know a majority of these
Excel sheets would have to have a common field added because the majority
don't have an identifying field as is.

My thought is with yours that this is "bigger then a breadbasket", which was
one of the reasons I wanted to ask on here to get some expert advice to back
my thinking since I'm only about an intermediate user. I really didn't have
a timeline to do this, this was just something she would like to have done,
but as I told her, I believe it would take an entire reworking of how
everything is done to even try to start to put it together.

Thanks for you input, I appreciate it.

 

[Pat Hartman \(MVP\)]

For starters, if you're going to do this, you would need to keep the data in
Access and from Access, produce any Word or Excel documents needed for
reporting. If the data comes to you from many places or people, you can use
Outlook Forms or Excel workbooks to gather the basic data and then import it
into Access. It is possible the much of the saving of documents can be
dispensed with once the data is in a single place.

Access can handle millions of rows if each row is not very large. If the
actual data is too large for Access or if you have too many concurrent users
(over ~ 25), you can use SQL server or another RDBMS as the back end while
still using Access as the front end.

It is seven years and three versions since A2K was released. Microsoft will
be discontinuing support for A2K in the not too distant future. It might be
time to upgrade to A2003 while copies are still available. Converting to
A2007 is a huge change due to the complete recreation of the user interface
and your company might not want to spring for the training involved with
that.

 

[Jeff Boyce]

As Pat points out, something the size you are describing probably needs to
use SQL-Server or some other upsized back-end database.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Shanin]

I was thinking SQL from the get go just due to the size it may become,
although I've never worked in that. You may not know the answer, but is it
hard to convert a backend Access table to SQL?

I also looked at Access 2007 and some of the features seem to be what may
work for the frontend, such as the clicking on info in a report to open a
form. We are thinking of upgrading our software anyway since it's been a
while. Reading about the lack of security in A07, I think we'd probably want
to go to a backend SQL anyway to secure the tables since we wouldn't want
every user to be able to access wage information of employees. Thanks for
the info

 

[Pat Hartman \(MVP\)]

If you think your BE will eventually become SQL Server, it is best to design
the application from the beginning using client/server concepts such as
forms bound to queries that include selection criteria to limit the data
retrieved by the form. Search here or the KB for articles on client/server
optimization so you understand that to make use of server-side processing,
you need to severely restrict the data retrieved.

Access 2007 does have some great new features and as long as you're not an
existing Access user, you shouldn't have any trouble with it. Old-timers
like myself are struggling because we have to keep a foot in each version
and switching back and forth is a real pain.

As far as security goes, desktop applications are frequently secured by
using the security included in Windows that restricts access to server
shares. You would keep the back end database on the server where only
authorized people could get to it and the front ends on the local machines.
What you loose with A2007 is the ability to secure objects in the database
itself. However, you can use A2007 as your development/runtime environment
but keep the application in the A2003 database format. That gives you the
advantage of the new development features but it still allows you to
implement replication and user-level security.

 

[Shanin]

I guess I really need to sit down with the people and see what they really
want incorporated to see what we really need for our backside. Unfortunatly
I have been using Access so I will be somewhat like you in taking a time to
get used to the new look if we go that route.

Wouldn't it, if I secured the folder on the backside so only I could view
it, it lock everyone from being able to access the data from their frontside,
or would it still work if the queries run with owner access? It seems I
tried that before, but it's been a while.

Thanks again.

 

[Tony Toews [MVP]]

I guess I really need to sit down with the people and see what they really
want incorporated to see what we really need for our backside. Unfortunatly
I have been using Access so I will be somewhat like you in taking a time to
get used to the new look if we go that route.

At the same time though don't promise the users that they will get
everything. Get them to tell you the one or two or three things that
they consider to be the highest priority and get those working. Get
them to choose a main contact which you can ask questions of on a
daily basis. That is a power user. And not someone who is into
management political bullsh*t gaming.

And get that going. See how they like it. Tweak it some and ask what
else they consider to be the next highest priority.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Pat Hartman \(MVP\)]

The OwnerAccess option is used with user level security - which is only
available with the .mdb format. A2007 does not support ULS with the new
..accdb access.

Securing the folder is a Windows function and any one with access to the
folder will be able to use your front end to access the back end in that
folder. Other folks may be able to get to your front end if it is in a less
secure place but they will not be able to retrieve any data from the BE
because they don't have the correct network permissions.

 

[Shanin]

I understand Windows security but since I've not seen A2007 live, I'm
confused on how I would do some things. Here is what I have on this
database. The backend is on the server in it's own file. The backend
includes all of my tables, such as tblemployee, tbltraining, tblwage, etc.
Everyone has to have Windows permission to the backend to even use the
database, but everyone does not need to have access to tblwage which is
truthfully the only table everyone does not need to see. Currently how it
is, everyone can get to the backend via Windows Explorer and see the file is
there, but only certain people can actually login to it and see any of the
tables, and of course on the frontend, they are locked out of certain forms
and reports. If I go to A2007 how would I continue to keep information from
the tblwage from being seen since it sounds like you can't actually secure an
item and I can't lock everyone off the backend. Is it possible to break that
table off into it's own database, create a link to it so I can still have a
relationship and then set Windows security on just that table in it's
database?

I know this has gotten away from the original topic a bit, but thanks for
all the input.