What are the limitations of using Excel as a presentation layer for
data in a relatively small MS SQL database that handles business
activities such as customer relationship management or discreet
manufacturing?

---

Request for Question Clarification by theta-ga on 22 Aug 2005 02:47 PDT

Hi infoatyourservice-ga,
  I have developed some applications that utilized on Excel as a front
end, and so have collected some information on the pros and cons of
doing this. Howeever, in order to better answer your question, it
would be helpful if you could provide the following information:
  - What version of Excel are you looking at using(Xp, 2003)?
  - How small is the this SQL database? (100's of records?1000's? 10,000? )
  - How many users are expected to use it at one time? 
  - How many transactions do you expect the application to perform
during the day? Are there any hard performance limits (such as say 100
transactions per hr) that you need the application to meet?
  - What is your target platform for the application? Windows or Mac or both?
 Once you post these requirements, I will be able to tailor the
informations to your needs, and post it as an answer.
Thanks,
Theta-ga
:)

With the information you provided I can only give the following answers:

a) Excel is limited to the amount of rows it can hold/display ( I
don't remember the number and it has changed from version to version,
but it IS limited)

b) If this is a server application that generate Excel files do know
that Excel is an out of process COM server which means ALL users of
the server application will use the SAME Excel process and since its
threading model is STA (Single Threaded Apartment) all requests to it
will be serialized.

c) If your code is written inside an Excel sheet (VBA code) you might
have some security issues that needs to be resolved by signing the
Excel file or lowering the security settings of Marco (less
recommened. I prefer signing the file).

If you'll give a bit more information about your application (is it a
client application or a server application, etc) and answer to the
question that the Researcher asked it would be helpful and will enable
me to provide more accurate information.