StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POArchitecture of an Excel application
text
Body
copied!<p>after 10 years of programming I find myself with the daunting task of creating my first Excel application in Excel 2007. I have programmed in VBA before on MS Access so this is not really a technical challenge for me but it's a real change of "paradigm" I dare say.</p> <p>Now I have to implement an Excel app that talks to SQLServer (on a dedicated database that I create), typical CRUD stuff but what I cannot read on any book (Excel Bible, Excel Power Programming, etc...) is how am I supposed to structure the app. </p> <ul> <li><p>Can I give names to my columns and use them as a database column when sending the data back to SQLServer</p></li> <li><p>When I retrieve back the data what's expected of a spreadsheet app that I for example retrieve ID, Description (hiding the ID in a column and showing the Description) or should I just use the Description for everything and store denormalized data in my SQLServer tables, making them the equivalent of a server side Excel sheet?</p></li> <li><p>If I want to use normalized data like for LookupTable (ID, Country) should I store the ID, Country information in a Range and if so, how do I force the user to pick a value from that Range (ID, Country) without using a proper Combobox?</p></li> <li><p>When I retrieve the data from SQLServer should I model it into an ADODB.Recordset (for example by calling a view or a stored procedure) and copy it to the Sheet making sure the order of fields in the Recordset is the same as in the Sheet or is there a better way?</p></li> </ul> <p>I'm sure there's many people that understand my situation out there because they had been in my shoes, please help me make the jump that will help me understand the spreadsheet app world. Pointers to web resources are very welcome too.</p> <p>Thanks.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload