Front End options

I need people's expertise on this. I'm currently at a
crossroad with my Access XP dbs. We're planning on moving
our access tables to SQL 2k. My boss wants me to look
into options for the Front End. The dbs are mission
critical (worked on 24/7) and would like to be more
flexible in the future to incorporate other things/info.
Currently I have an Access XP front end. I was wondering
what would be the best front end to use with SQL?
Here is what I found out so far and I'm not sure if my
findings are even accurate.
1. Access ADP - more efficient than mdb since it doesn't
use the jet engine. Less work because I don't have to
remake all the forms/reports/etc... I don't know too much
of the advantages of ADP.
2. Webpage - faster in speed than Access. I already have
several pages that access the sql server. A lil more of a
learning curve because I would have to get into
CSS/frames/javascripts because my current db has lots of
forms, subforms, reports and subreports to convert over.
3. C++ or C# - someone mentioned it was the way to go in
the future. But I don't have any knowledge in that and it
would be a big learning curve and maybe not for this big
of a project.
4. VB - speeds faster but more work than Access.
Learning curve would be a lil much too.
Anyone can give their input to help me make a decision on
which way to go?
Thanks.
Ngan
I can't really help you choose because I don't know enough about your
application, but I will point out a few things with your numbered list
below:
1. Not true. an mdb can be just as efficient as an adp, and you would
have to redo forms and reports because you'd probably have to redo the
underlying queries. You give up the flexibility of local storage for
static data and heterogeneous joins. Regardless of mdb or adp, you'll
need to redo the front end app to fetch less data and rewrite most of
your saved queries as stored procedures/views.
2. ASP.NET is a good choice if it's a web app. Forget about easy
subforms/subreports for any web FE you might choose. Only Access does
subforms well.
3. If you go the .NET route, you don't need to learn C#, much less
C++. VB.NET is just as good and the learning curve won't be so high
for you. However, Windows forms for database apps don't have all of
the features you like in Access (subforms again), and working with
data is totally alien to the Access way of doing things.
4. VB6 would be a step back, more work, no real advantage unless you
factor in ease of deployment. You'll still need to rewrite those
queries. No subforms.
My recommendation would be to redo your Access mdb FE to be more
efficient against a SQL Server BE by fetching less data and using
stored procedures for reports, etc. You can cache static data for
combo boxes and dynamically load subform data in local tables.
Focusing on fixing whatever is wrong with your Access mdb in terms of
table and application design to make it more efficient when you
migrate the data to SQL Server will be most productive use of your
time. SQL Server has a very high learning curve, and you'd be better
off mastering SQLS first rather than trying to do that at the same
time as learning another programming language or FE application.
--Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Tue, 10 Aug 2004 13:19:38 -0700, "Ngan"
<anonymous@.discussions.microsoft.com> wrote:

>I need people's expertise on this. I'm currently at a
>crossroad with my Access XP dbs. We're planning on moving
>our access tables to SQL 2k. My boss wants me to look
>into options for the Front End. The dbs are mission
>critical (worked on 24/7) and would like to be more
>flexible in the future to incorporate other things/info.
>Currently I have an Access XP front end. I was wondering
>what would be the best front end to use with SQL?
>Here is what I found out so far and I'm not sure if my
>findings are even accurate.
>1. Access ADP - more efficient than mdb since it doesn't
>use the jet engine. Less work because I don't have to
>remake all the forms/reports/etc... I don't know too much
>of the advantages of ADP.
>2. Webpage - faster in speed than Access. I already have
>several pages that access the sql server. A lil more of a
>learning curve because I would have to get into
>CSS/frames/javascripts because my current db has lots of
>forms, subforms, reports and subreports to convert over.
>3. C++ or C# - someone mentioned it was the way to go in
>the future. But I don't have any knowledge in that and it
>would be a big learning curve and maybe not for this big
>of a project.
>4. VB - speeds faster but more work than Access.
>Learning curve would be a lil much too.
>Anyone can give their input to help me make a decision on
>which way to go?
>Thanks.
>Ngan
|||Thanks Mary for your insight. I actually bought your
book
1. Does a mdb FE work well regardless of how many tables
there are and how many records in those tables? I
currently have about 40 tables and the tables that most
people write to have over 100,000 records each.
2. What about the number of concurrent users using the
mdb FE with the SQL BE? Currently with my access dbs,
once in awhile I get record corruption (mostly because of
the memo fields). I may have up to 50 concurrent users
(reading data and/or writing data).
If mdb works as well as any FE app and I work on fixing
the bottleneck, can it hold up to the number of users and
records?
Thanks!
Ngan

>--Original Message--
>I can't really help you choose because I don't know
enough about your
>application, but I will point out a few things with your
numbered list
>below:
>1. Not true. an mdb can be just as efficient as an adp,
and you would
>have to redo forms and reports because you'd probably
have to redo the
>underlying queries. You give up the flexibility of local
storage for
>static data and heterogeneous joins. Regardless of mdb or
adp, you'll
>need to redo the front end app to fetch less data and
rewrite most of
>your saved queries as stored procedures/views.
>2. ASP.NET is a good choice if it's a web app. Forget
about easy
>subforms/subreports for any web FE you might choose. Only
Access does
>subforms well.
>3. If you go the .NET route, you don't need to learn C#,
much less
>C++. VB.NET is just as good and the learning curve won't
be so high
>for you. However, Windows forms for database apps don't
have all of
>the features you like in Access (subforms again), and
working with
>data is totally alien to the Access way of doing things.
>4. VB6 would be a step back, more work, no real advantage
unless you
>factor in ease of deployment. You'll still need to
rewrite those
>queries. No subforms.
>My recommendation would be to redo your Access mdb FE to
be more
>efficient against a SQL Server BE by fetching less data
and using
>stored procedures for reports, etc. You can cache static
data for
>combo boxes and dynamically load subform data in local
tables.
>Focusing on fixing whatever is wrong with your Access mdb
in terms of
>table and application design to make it more efficient
when you
>migrate the data to SQL Server will be most productive
use of your
>time. SQL Server has a very high learning curve, and
you'd be better
>off mastering SQLS first rather than trying to do that at
the same
>time as learning another programming language or FE
application.[vbcol=seagreen]
>--Mary
>Microsoft Access Developer's Guide to SQL Server
>http://www.amazon.com/exec/obidos/ASIN/0672319446
>On Tue, 10 Aug 2004 13:19:38 -0700, "Ngan"
><anonymous@.discussions.microsoft.com> wrote:
moving[vbcol=seagreen]
wondering[vbcol=seagreen]
doesn't[vbcol=seagreen]
much[vbcol=seagreen]
have[vbcol=seagreen]
a[vbcol=seagreen]
in[vbcol=seagreen]
it[vbcol=seagreen]
on
>.
>
|||1 -- As long as you don't link to the entire table or try to bind
forms to an entire table. Create a query-by-form UI so that users must
select only a single record to edit data. Use Jet for storing lookup
tables and subform data.
2 -- 50 concurrent users editing data is absolutely not a problem for
SQL Server if you follow my recommendations in (1). If you've been
supporting that many in Access then your table design is probably
sound in the sense that you've eliminated concurrency conflicts by
ensuring that users never (or rarely) edit the same row at the same
time.
Since you already bought the book, look up the chapter on creating
unbound applications. The idea there is that you have a lightweight
front end, fetch minimal data read-only and post changes in Update and
Insert statements. You also have to handle concurrency issues in your
code, but you can just *borrow* the code we've already written. It
very much mimics the way a web application works. Using that
technique, you could easily support hundreds of concurrent users.
--Mary
On Wed, 11 Aug 2004 08:41:58 -0700, "Ngan"
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks Mary for your insight. I actually bought your
>book
>1. Does a mdb FE work well regardless of how many tables
>there are and how many records in those tables? I
>currently have about 40 tables and the tables that most
>people write to have over 100,000 records each.
>2. What about the number of concurrent users using the
>mdb FE with the SQL BE? Currently with my access dbs,
>once in awhile I get record corruption (mostly because of
>the memo fields). I may have up to 50 concurrent users
>(reading data and/or writing data).
>If mdb works as well as any FE app and I work on fixing
>the bottleneck, can it hold up to the number of users and
>records?
>Thanks!
>Ngan
>
>enough about your
>numbered list
>and you would
>have to redo the
>storage for
>adp, you'll
>rewrite most of
>about easy
>Access does
>much less
>be so high
>have all of
>working with
>unless you
>rewrite those
>be more
>and using
>data for
>tables.
>in terms of
>when you
>use of your
>you'd be better
>the same
>application.
>moving
>wondering
>doesn't
>much
>have
>a
>in
>it
>on
sql