Hi All,

I need your suggestions: I have a software that uses MS Access
as a front end, and has MS SQL as a back end database. SQL Server is
running on approximately 2 GB of RAM. Database size is approximately 1
million records.

Problem I am having is that when I do queries using the front end. The
application responds very slowly on the network. I understand that I
am probably not providing enough information about the problem. But
can someone please comment on the following

1) Is it good idea to have MS Access as front end and SQL Server as a
back end?
2) if not what may be causing this slowness?
3) What are few things that I can look into for performance testing?
5) are there any know issues with ACCSS/SQL server combination?
6) I understand that SQL server may also be contributing to this poor
Performance, what are a few things that I can check to see about
this?
7) What measures should I take bafore to decide, whether I want to fix
the old
One or invest money on getting a new one? What should be a deciding
factor,
Considering that I do have some cash in hand for possibly a new off
the shelf
Product.
8) If its network related, what kind of testing do think I can do?

Any Ideas, Help, Comments, by the ways.. all appreciated.

Thanks all for your help in advance.

Nauman





form on a software that

RE/
A lot of people would say it's the Good Right And Holy Path for mission-critical
applications. Defintatley nothing wrong with it performance-wise unless the
machine hosing the DB is overtaxed.

Reading between the lines, it sounds like you're running MS Access queries
through ODBC.

Have you tried writing a stored procedure and calling it via ADO?

Also, a middle-of the road approach: Make the problem query into a
"Passthrough" query and, if necessary, tune the SQL for SQL Server.

Finally, somebody should check the joins in the problem query against the
indexes in the SQL Server DB.

--
PeteCresswell

Another solution, somewhat similar:
Copy your Access query and rebuild it as a view in SQL Server.
It likely will run faster there, and you can link to the view as you
would link to a SQL Server table.

Bruce Pick
+++++++++++++++++++++++
"Nauman" <ncheema (AT) mtechnet (DOT) com> wrote