[AccessD] From a reader

_______________________________________________________________________________________

Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________



Well, there are a few things that come immediately to mind.  Using a MS Access FE with SQL backend will work great (fast, reliable etc) but she is going to need to understand how you need to set up access and the connection to the SQL Server using ADO and the connection strings.

Using linked tables and bound forms are going to cripple performance and probably reliablility as well.  Not linked tables and no bound forms.

Each user should have their own FE version (locked down as an MDE in the old language).  The Front end should basically be an empty shell with unbound forms.  You only pull in the data you need, when you need it and absolutely make the stored procs on the SQL Server do all the heavy lifting.  Understand how to use pass thru queries to pull data into Access.

You should only push (write) back to the server anything that has been changed and needs to be updated.  Normally much of the data can be pulled in as read only anyway, this goes for combo box data as well (again I pull into Access from the server using a Just in Time approach).

Sharepoint isn't going to help at this stage, although I believe that Access 2010 is rather neat with sharepoint integration.  I have no experience of it though, just what I have read.

If you want true web based, you really should just bite the bullet and use a C#.net (say ASP.net) front end to SQL Server back.  ok, it will take time and money to develop, but once you have it in place it will deliver.

This reminds me of the ol' business triangle.  Choose any two options, but lose the 3rd option.  Cheap, Fast, Good.

I hope she can go for "Good" and "Fast" and make the investment in effort/money.  Of course that is not always an option...  Be good to read what others have to say.

cheers
Darryl.




 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: Tuesday, 1 February 2011 12:22 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] From a reader

I've been corresponding (lightly) with a reader who needs to upsize an Access 2007 database to SQL Server -- ultimately, she's looking for a web solution. It sounds like an excruciating application -- she said it takes hours to run queries. I think she's looking for two things. First, she wants something to analyze the Access database to make it more efficient. (I haven't asked who built it to begin with, her or a professional developer.) I told her to start with the utilities already there, the performance and table analyzers. Are there any third-party products that do more or work better? Second, she wants a plug-in GUI -- I've never heard of such a thing, but I'll let you guys read her request and if you have something to suggest, I'll relay it. Thanks!

Susan H. 
"What I am asking is: Is there a design solution that would make a large sluggish access application scalable, faster, easier to distribute to remote sites? All that I have read to date - is that SQL Server as the best and most practiced solution. But, I still see that as "tons" of data still being pushed between ACCESS and SQL Server - so how is that really better. Is there a WEB or Sharepoint solution that would work as the ACCESS GUI  front-end and a backend SQL Server to crunch the billions of rows into the summary levels of data?"
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________________________________________________

The information transmitted in this message and its attachments (if any) is intended 
only for the person or entity to which it is addressed.
The message may contain confidential and/or privileged material. Any review, 
retransmission, dissemination or other use of, or taking of any action in reliance 
upon this information, by persons or entities other than the intended recipient is 
prohibited.

If you have received this in error, please contact the sender and delete this e-mail 
and associated material from any computer.

The intended recipient of this e-mail may only use, reproduce, disclose or distribute 
the information contained in this e-mail and any attached files, with the permission 
of the sender.

This message has been scanned for viruses.
_______________________________________________________________________________________