Some design input needed..the start of a great quest

Hello, everyone! I'm starting on a great quest here. I'll start by explaining
the big picture, then break it down to baby steps.

Big Picture-
First, I work in a lab where I test warranty returns for a product. We used
to track all this information in a spreadsheet for eack product. We got
bigger and this got overwhelming.

Last year, we migrated to MS Access. So now, I enter all my data into one
form for all products. This database is now HUGE. We have up to 5 people (and
testers) with access open for this one .mdb file while others are looking at
tables on the back end. It's slow, sluggish and we are starting to see
corruption now up two twice a month.

Time to take the database to the next level.

What's is coming up-
The next year or so, I will be having many new products plus the old ones
that are going to require all kinds of good information logged. Volumes will
be mind blowing. This current method will not support me.

What I'm thinking of-
We have data analysists that will still want to use access. That's cool, I
understand that. Why can't I have a remote database on a server where I can
pipe all my product information too? I can then have all this massive
information stored that people can link to to pull the information they need.
No interruptions with the dataflow on my end (most of this information is
going by the way of automation to increase speed).

That is where I am thinking of going.


Baby steps-------
So what is the best way to go about this? .ASP, SQL. I'm really confused, I
have visual Studio in the lab and there seems like a dozen ways I can go
about this. All are complicated and confusing. I"m great at learning and with
research, so I'm hoping to get a clear, defined direction here and see what I
can go learn.

This will be a great quest. Very cool stuff. Thanks in advance

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200902/1

you're assuming that you need to move the data out of an Access database in
order to see improvements. if you're using the database over a WAN, that's
probably true, because Access does not perform well in that environment. but
if your users are all on a LAN, i'd recommend you overhaul your existing
Access database, because it's likely that poor design - in the user
interface, and quite possibly in the tables/relationships - is causing your
problems, not the size of the database itself, or the number of users.

So what is the best way to go about this? .ASP, SQL. I'm really confused,
I
have visual Studio in the lab and there seems like a dozen ways I can go
about this. All are complicated and confusing.

Access is also complex, but not that confusing unless you try to leap tall
buildings before you've learned to crawl, walk, run, and then jump. it's a
powerful tool, but it has a steep learning curve; don't expect to redesign
your database and user interface in a day or two.

recommend you start at the beginning, with relational design principles.
study that topic until you understand it, then make what changes are
necessary in the table/relationships of *a COPY of your live database* to
meet those guidelines. then split the COPY database - backend
(tables/relationships only) in a database on the local server. link those
tables into a new frontend database, and build the queries, forms, reports,
macros, and modules of the user interface there. the final step will be to
migrate the "live" data from the old database into the new backend, then put
a copy of the frontend database on each user's PC.

hth


"chris23892 via AccessMonster.com" u39649@uwe wrote in message
news:915afc594ec8e@uwe...
Hello, everyone! I'm starting on a great quest here. I'll start by
explaining
the big picture, then break it down to baby steps.

Big Picture-
First, I work in a lab where I test warranty returns for a product. We
used
to track all this information in a spreadsheet for eack product. We got
bigger and this got overwhelming.

Last year, we migrated to MS Access. So now, I enter all my data into one
form for all products. This database is now HUGE. We have up to 5 people
(and
testers) with access open for this one .mdb file while others are looking
at
tables on the back end. It's slow, sluggish and we are starting to see
corruption now up two twice a month.

Time to take the database to the next level.

What's is coming up-
The next year or so, I will be having many new products plus the old ones
that are going to require all kinds of good information logged. Volumes
will
be mind blowing. This current method will not support me.

What I'm thinking of-
We have data analysists that will still want to use access. That's cool, I
understand that. Why can't I have a remote database on a server where I
can
pipe all my product information too? I can then have all this massive
information stored that people can link to to pull the information they
need.
No interruptions with the dataflow on my end (most of this information is
going by the way of automation to increase speed).

That is where I am thinking of going.


Baby steps-------
So what is the best way to go about this? .ASP, SQL. I'm really confused,
I
have visual Studio in the lab and there seems like a dozen ways I can go
about this. All are complicated and confusing. I"m great at learning and
with
research, so I'm hoping to get a clear, defined direction here and see
what I
can go learn.

This will be a great quest. Very cool stuff. Thanks in advance

--
Message posted via AccessMonster.com

http://www.accessmonster.com/Uwe/For...esign/200902/1

Excellent! Thank you the good ideas.

I'm not the DB administrator, therefore I can not overhaul what we have. I
want to basically put all of my data I have to collect in a table on a server
and use your idea.

tina wrote:
you're assuming that you need to move the data out of an Access database in
order to see improvements. if you're using the database over a WAN, that's
probably true, because Access does not perform well in that environment. but
if your users are all on a LAN, i'd recommend you overhaul your existing
Access database, because it's likely that poor design - in the user
interface, and quite possibly in the tables/relationships - is causing your
problems, not the size of the database itself, or the number of users.

So what is the best way to go about this? .ASP, SQL. I'm really confused, I
have visual Studio in the lab and there seems like a dozen ways I can go
about this. All are complicated and confusing.

Access is also complex, but not that confusing unless you try to leap tall
buildings before you've learned to crawl, walk, run, and then jump. it's a
powerful tool, but it has a steep learning curve; don't expect to redesign
your database and user interface in a day or two.

recommend you start at the beginning, with relational design principles.
study that topic until you understand it, then make what changes are
necessary in the table/relationships of *a COPY of your live database* to
meet those guidelines. then split the COPY database - backend
(tables/relationships only) in a database on the local server. link those
tables into a new frontend database, and build the queries, forms, reports,
macros, and modules of the user interface there. the final step will be to
migrate the "live" data from the old database into the new backend, then put
a copy of the frontend database on each user's PC.

hth

Hello, everyone! I'm starting on a great quest here. I'll start by explaining
the big picture, then break it down to baby steps.
[quoted text clipped - 35 lines]

This will be a great quest. Very cool stuff. Thanks in advance

http://www.accessmonster.com/Uwe/For...esign/200902/1

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200902/1

you're welcome, good luck with it.


"chris23892 via AccessMonster.com" u39649@uwe wrote in message
news:915df5d8e0dd1@uwe...
Excellent! Thank you the good ideas.

I'm not the DB administrator, therefore I can not overhaul what we have. I
want to basically put all of my data I have to collect in a table on a
server
and use your idea.

tina wrote:
you're assuming that you need to move the data out of an Access database
in
order to see improvements. if you're using the database over a WAN,
that's
probably true, because Access does not perform well in that environment.
but
if your users are all on a LAN, i'd recommend you overhaul your existing
Access database, because it's likely that poor design - in the user
interface, and quite possibly in the tables/relationships - is causing
your
problems, not the size of the database itself, or the number of users.

So what is the best way to go about this? .ASP, SQL. I'm really
confused, I
have visual Studio in the lab and there seems like a dozen ways I can
go
about this. All are complicated and confusing.

Access is also complex, but not that confusing unless you try to leap
tall
buildings before you've learned to crawl, walk, run, and then jump. it's
a
powerful tool, but it has a steep learning curve; don't expect to
redesign
your database and user interface in a day or two.

recommend you start at the beginning, with relational design principles.
study that topic until you understand it, then make what changes are
necessary in the table/relationships of *a COPY of your live database* to
meet those guidelines. then split the COPY database - backend
(tables/relationships only) in a database on the local server. link those
tables into a new frontend database, and build the queries, forms,
reports,
macros, and modules of the user interface there. the final step will be
to
migrate the "live" data from the old database into the new backend, then
put
a copy of the frontend database on each user's PC.

hth

Hello, everyone! I'm starting on a great quest here. I'll start by
explaining
the big picture, then break it down to baby steps.
[quoted text clipped - 35 lines]

This will be a great quest. Very cool stuff. Thanks in advance


http://www.accessmonster.com/Uwe/For...esign/200902/1

--
Message posted via AccessMonster.com

http://www.accessmonster.com/Uwe/For...esign/200902/1

Comments in line:

"chris23892 via AccessMonster.com" u39649@uwe wrote in message
news:915afc594ec8e@uwe...
Hello, everyone! I'm starting on a great quest here. I'll start by
explaining
the big picture, then break it down to baby steps.

Big Picture-
First, I work in a lab where I test warranty returns for a product. We
used
to track all this information in a spreadsheet for eack product. We got
bigger and this got overwhelming.

Last year, we migrated to MS Access. So now, I enter all my data into one
form for all products. This database is now HUGE. We have up to 5 people
(and
testers) with access open for this one .mdb file while others are looking
at
tables on the back end. It's slow, sluggish and we are starting to see
corruption now up two twice a month.

Define HUGE. What you think to be HUGE might be SMALL or MEDIUM for other
peoples. The fact that your database is now sluggish is not necessarily
related to the fact that it's *really huge*; as there are other
possibilities like missing indexes, bad design, too much data retrieved each
time, etc.

You should also define what you mean with "corruption"; as there are also
many possibles senses/ explanations/ solutions for this word, too.

Time to take the database to the next level.

What's is coming up-
The next year or so, I will be having many new products plus the old ones
that are going to require all kinds of good information logged. Volumes
will
be mind blowing. This current method will not support me.

What I'm thinking of-
We have data analysists that will still want to use access. That's cool, I
understand that. Why can't I have a remote database on a server where I
can
pipe all my product information too? I can then have all this massive
information stored that people can link to to pull the information they
need.
No interruptions with the dataflow on my end (most of this information is
going by the way of automation to increase speed).

No problem doing that with Access. SQL-Server is less prone to corruption
than Access but in general, if you have a problem with a tool, it's must
likely because of the person using this tool than because of the tool
itself.

That is where I am thinking of going.

Baby steps-------
So what is the best way to go about this? .ASP, SQL. I'm really confused,
I
have visual Studio in the lab and there seems like a dozen ways I can go
about this. All are complicated and confusing. I"m great at learning and
with
research, so I'm hoping to get a clear, defined direction here and see
what I
can go learn.

Keep learning: this is always the best first step that you can ever make for
about everything.

This will be a great quest. Very cool stuff. Thanks in advance

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200902/1

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)