Mombu the Programming Forum sponsored links

Go Back   Mombu the Programming Forum > Programming > MS Access Front End, SQL Server Back End
User Name
Password
REGISTER NOW! Mark Forums Read

sponsored links


Reply
 
1 30th April 08:32
ncheema
External User
 
Posts: 1
Default MS Access Front End, SQL Server Back End


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
  Reply With Quote


  sponsored links


2 30th April 08:32
(pete cresswell)
External User
 
Posts: 1
Default MS Access Front End, SQL Server Back End


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
  Reply With Quote
3 20th May 05:10
bruce pick
External User
 
Posts: 1
Default MS Access Front End, SQL Server Back End


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
+++++++++++++++++++++++
  Reply With Quote
Reply


Thread Tools
Display Modes




Copyright 2006 SmartyDevil.com - Dies Mies Jeschet Boenedoesef Douvema Enitemaus -
666