General discussion

Locked

Query design on Access/SQL database

By AJD ·
I have an Access database that has outgrown Access 2000 and needs to be moved to SQL Server. I am going to retain the Access forms I have developed as the front end.

My question is: In order to optimise performance and data integrity, should thedatabase queries be run on the client in the Access 2000 front end, or on the SQL server as Views?

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Query design on Access/SQL database

by Jay Eckles In reply to Query design on Access/SQ ...

without a doubt as views on the SQL Server if that's where the data is being stored.

Collapse -

Query design on Access/SQL database

by AJD In reply to Query design on Access/SQ ...

Poster rated this answer

Collapse -

Query design on Access/SQL database

by Dushy_ant In reply to Query design on Access/SQ ...

The queries in SQL have to be Parsed and optimised before they are executed.
Views are actually parsed queries which need not be optiomised before they are run. Hence they are good in Increasing performance

Collapse -

Query design on Access/SQL database

by AJD In reply to Query design on Access/SQ ...

Poster rated this answer

Collapse -

Query design on Access/SQL database

by saratogapete In reply to Query design on Access/SQ ...

AJD - We feel your pain but we do this quite often in our practice.

First off, yes, you should move up (upsize) to SQL Server 2000. Good decision. Secondly, you need to broaden your understanding a bit in order to take advantage of SQL Server 2000. What you want to do is to "off load" the preparation of data for a form or a report onto the server as opposed to the client.

Creating a VIEW in SQL Server (which you can see if you create an ADP in Access 2000 or 2002) is very much like aquery in Access. The differences is you can't sort, filter, etc. Very rigid but not very flexibly.

You want to move to understanding how to use Stored Procedures in SQL Server 2000. If this is new territory for you I would suggest you mapping out the business rules and then working with an experience SQL Server person to create one stored procedure at a time.

Once created, you can bind a stored procedure to be the RecordSource for a Form or Report in the Access front end. This is very powerful stuff!!

To echo the other respondent, you will have to do some serious thinking of your application and rework accordingly. Just do it methodically and with LOTS of planning and specs.

Good luck.

Collapse -

Query design on Access/SQL database

by AJD In reply to Query design on Access/SQ ...

Thanks for the advice: perhaps you can help me with my next question!
http://www.techrepublic.com/forumqa/thread_detail.jhtml?thread_id=78002

Collapse -

Query design on Access/SQL database

by AJD In reply to Query design on Access/SQ ...

This question was closed by the author

Back to Web Development Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums