Need help choosing front end for SQL Server

[PsychicStickleBrick]

I've been working on an Access 97 database that's pretty much reached
it's limit in terms of performance and reliability. Although it supports
relatively few users (5-10 concurrent) it contains a lot of data (around
30 tables, some with several million records) plus a lot of forms and
reports.

The company has recently purchased an MS SQL server and it's my job to
migrate the back-end as well as re-write the front-end (the front-end
needs re-writing anyway).

My question is do we keep Access as the front end (we'd probably upgrade
to 2003) or would we get better performance using a different solution
(VB.Net for example) ?
If Access, would it be best use MDB's or ADP's?

The main consideration is that we need to perform some complicated
queries on large datasets, which ideally should happen on the SQL server
(I'm currently learning about stored procedures and views etc).

Thanks,

PSB

 

[Albert D. Kallal]

I see no reason why you can't continue to use access as the front end.

If you have the budgets, and time to re-write the application, then I guess
you
can choose anything you like.

However, if you wish to keep the application, and not re-write, and not have
to re-train the users, then why not continue to use access as the front end?

My question is do we keep Access as the front end (we'd probably upgrade
to 2003)

This is going to be up to you. I see no reason why not to continue to use
ms-access. I mean, you got to write the application in something.

or would we get better performance using a different solution

For the number of users you have, I see no reason not to use ms-access.

(VB.Net for example) ?

It is a great platform. Do you have the developers...or perhaps you will
spend the time and learn .net? (you will need a few months of time). I mean,
how long did it take the people to learn ms-access? (it took me more then a
year to be become productive in ms-access). And, the sample applies to any
platform you will adopt.

If Access, would it be best use MDB's or ADP's?

It is by far and away best to keep it as a MDB (and, of course, you used a
split database..and always distributed a mde to each user anyway..right??).

I would only choose a ADP for a brand new from scratch product (and even
then...likely I would not use a adp). So, for a existing application, it
makes NO
sense to use a ADP..as you can't use any of your DAO code.

The main consideration is that we need to perform some complicated
queries on large datasets, which ideally should happen on the SQL server
(I'm currently learning about stored procedures and views etc).

Yes, for processing intensive things, you certainly want that to run on the
server, but you still need to choose a programming environment that lets you
write code, and create the application (and sql server does not do that!!).

If you got the time,a nd resources, and have the developer(s) to re-write,
then you can do such. It really comes down to budgets, and what benefits
you get by re-writing the application.

The skill level of the developer will make or break the project regardless
of the "conversion" issues.

I am a better hockey coach when Wayne Gretzky is on the team.

Hence the #1 consideration is at what level the developer is at. There are
certainly more levels then just "trained" or "not trained". Generally there
are a "lot" of skill levels, but the following breakdown is sufficient. **

Stage 1 Innocent (never heard of the product)

Stage 2 Aware (Has read an article about X)

Stage 3 Apprentice (has attended a three-day seminar)

Stage 4 Practitioner (ready to use X on a real project)

Stage 5 Journeyman (uses X naturally and automatically in his job)

Stage 6 Master (has internalized X, knows when to break the rules)

Stage 7 Expert (writes books, gives lectures, looks for ways to extend x)


One should NEVER attempt a project with a team consisting with Stage 3 or
lower people. This is a sure fire formula for failure. The team can consist
of stage 4's, but they should have at least access to Stage 5, or 6.

So, if your developers are skilled in the new platform, then that is
certainly a possible here to re-write.

However, I see no reason why you can't continue to use ms-access as the
front end to sql server. I mean, if your developers don't know how to use
sql server, then you can get a garbage and slow application if it is written
in VB, vb.net, or ms-access. The performance here is not going to be the
fact that you used VB or ms-access, but in fact that your developers know
how to utilize sql server.

 

[Norman Yuan]

For that number of users, Access should be able to handle if designed
properly (Seperated FE and BE and BE file size is not too big, say close 2G
limit). However, now you have already bought SQL Server, and as you stated
"the front-end needs re-writing anyway", so, there is doubt of moving to SQL
Server.

As for front end, to use Access, or not to use it, lisenceing Access itself
would not be a big problem here. That is, if you use Access FE, all users
must have it installed. for $300 of each, the cost does not weigh too much
in the entire situation. And for your situation (fairly small number of
user, and I'd imagine they are in the same office, so deploy/update your app
wouldn't be a serious issue), exactly what type of FE is not that important.

I do not know your skill level, but programming a non-Access front end (Win
App ot wep app) is a lot more different from Access programming and requires
quite different skill and knowledge. Whether such en expertise is available
or not would make quite big difference on development cost.

If you go with Access, probably ADP is the option (But you must use
Access2K or later, not Access97). And many experienced Access developers
claim they can produce Access solution a lot faster than other type of FE.

 

[Tom Collins]

Now there's the hundred thousand dollar question. The answer is it depends.
Moving the back-end to Sql Server is a good idea. And you can do this with
very little re-writing of the front end.

As for the front-end, it depends on how much of it needs re-written. If it's
just a little, then I'd probably stay with Access. You would get a little
performance hit, but not much if the queries ran server side.
If it's a lot of re-write, then I would go with VB.NET (or ASP.NET if you
want to make it a web app). You get a lot more functionality with these.
One word of note. Running a VB.NET app requires the Microsoft Framework also
be installed. This is a bit resource heavy and will run slowly on older
machines.

Good Luck.

Tom Collins

 

[Dirk Goldgar]

I've been working on an Access 97 database that's pretty much reached
it's limit in terms of performance and reliability. Although it
supports relatively few users (5-10 concurrent) it contains a lot of
data (around 30 tables, some with several million records) plus a lot
of forms and reports.

The company has recently purchased an MS SQL server and it's my job to
migrate the back-end as well as re-write the front-end (the front-end
needs re-writing anyway).

My question is do we keep Access as the front end (we'd probably
upgrade to 2003) or would we get better performance using a different
solution (VB.Net for example) ?
If Access, would it be best use MDB's or ADP's?

The main consideration is that we need to perform some complicated
queries on large datasets, which ideally should happen on the SQL
server (I'm currently learning about stored procedures and views etc).

Thanks,

PSB

My immediate reaction would be to stay with Access as your front-end,
and use an MDB for it. Some aspects of the application may need to be
rewritten, but in my experience that will probably be much quicker than
rewriting the whole thing in another devleopment environment. Moving
complicated queries to the server, though, is likely to take a fair
amount of work, and you have to budget for that. If the queries don't
use VBA functions or the First() or Last() functions, I don't think
you'll have too many problems, but if they do you'll have to figure out
how to accomplish the same function using SQL Server features and T-SQL.

Incidentally, one thing to watch out for -- and I know because it's
bitten me a few times -- is that your queries and application logic may
currently make assumptions about the order in which records are returned
in the absence of an ORDER BY clause. For example, in Jet SQL the GROUP
BY clause implicitly orders the records, but in T-SQL it doesn't. I
mention this only as something to keep in mind as you transfer queries
to SQL Server.

 

[onedaywhen]

in Jet SQL the GROUP
BY clause implicitly orders the records, but in T-SQL it
doesn't.

Jet implicitly orders using what criteria?

Jamie.

--

 

[Rick Brandt]

Jet implicitly orders using what criteria?

Jamie.

Alphabetical/Numercal IOW "GroupBy SomeField" implicitly adds an "ORDER BY
SomeField" in Access/Jet.

 

[Steve Thompson]

Jet implicitly orders using what criteria?

As I recall Jet orders the result set by the table's PK.

Steve

 

[Brendan Reynolds]

When you use GROUP BY in the absence of an ORDER BY clause Jet will sort by
the columns used in the GROUP BY clause, from left to right. For example,
this query ...

SELECT tblTest.TestText, tblTest.TestNumber, tblTest.ID
FROM tblTest
GROUP BY tblTest.TestText, tblTest.TestNumber, tblTest.ID;

.... is equivalent to this query ...

SELECT tblTest.TestText, tblTest.TestNumber, tblTest.ID
FROM tblTest
GROUP BY tblTest.TestText, tblTest.TestNumber, tblTest.ID
ORDER BY tblTest.TestText, tblTest.TestNumber;

.... however in this query ...

SELECT tblTest.TestText, tblTest.TestNumber, tblTest.ID
FROM tblTest
GROUP BY tblTest.TestText, tblTest.TestNumber, tblTest.ID
ORDER BY tblTest.ID;

.... the explicit ORDER BY clause over-rides the implicit ordering of the
GROUP BY clause.

 

[onedaywhen]

When you use GROUP BY in the absence of an ORDER BY clause Jet will sort by
the columns used in the GROUP BY clause, from left to right.

Thanks everyone. I got round to testing and I concur the following:

- the implicit ORDER BY is by columns left to right, ascending;
- the process occurs in Jet rather than MS Access;
- it is unaffected by clustered index, primary key, date/time entered
order etc.

Thanks again,
Jamie.

--