Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I've written a couple of applications similar to the one you've described and while I can't pretend to offer <em>best practice</em> I can comment based on my personal experiences.</p> <p>• Can I give names to my columns and use them as a database column when sending the data back to SQLServer</p> <p><em>Sure why not, you can add the column names as the first row of an excel sheet and use the cell protection features to prevent tampering by the end user. You need to associate the columns of data in excel with the underlying fields in SQL Server somehow and this is as good a way as any.</em></p> <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> <p><em>You can create individual (hidden) sheets for each lookup table and enforce selection using code something like this (based on a macro recording so this could be cleaned up)</em></p> <pre><code> With Sheet1.Range("E3").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=B2:B5" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "A sample" .ErrorTitle = "An error" .InputMessage = "Input message" .ErrorMessage = "Error message" .ShowInput = True .ShowError = True End With </code></pre> <p><em>This will give you a combo box in cell, an error message on failed validation and a title for the cell.</em></p> <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> <p><em>It would work either way. I’d probably decide based on the complexity of the data. If you’re needing to build lots of validation code in VBA I’d be tempted to pull denormalised data into staging tables in SQL Server and then denormalise using stored procs before moving the data to your main tables. YMMV.</em></p> <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> <p><em>I'd avoid using copy and paste as it uses a system wide clipboard and odd things can start happening if you're using the PC at the sametime. The range object in excel has a CopyFromRecordset method that you can use to push the data from ADO to a worksheet. It's a lot faster than iterating through the recordset assigning manually. The only downside is that you'll have to create the header columns yourself beforehand.</em></p>
    singulars
    1. This table or related slice is empty.
    1. POArchitecture of an Excel application
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USSteve Homer
    1. USSteve Homer
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POArchitecture of an Excel application
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks Homer, great answer, what i'm trying to say in the last point is that when i retrieve my denormalized table from the database i should "paste" it on for example range (A1) assuming the excel sheet and sqlserver have the same structure.
      singulars
      1. PO
      1. USLorenzo
    2. COAh okay I see. I'd avoid using copy and paste as it uses a system wide clipboard and odd things can start happening if you're using the PC at the sametime. The range object in excel has a CopyFromRecordset method that you can use to push the data from ADO to a worksheet. It's a lot faster too. The only downside is that you'll have to create the header columns yourself beforehand.
      singulars
      1. PO
      1. USSteve Homer
    3. COFrom here: http://support.microsoft.com/kb/257819 Column headings: By default, it is assumed that the first row of your Excel data source contains columns headings that can be used as field names. If this is not the case, you must turn this setting off, or your first row of data "disappears" to be used as field names. This is done by adding the optional HDR= setting to the Extended Properties of the connection string. The default, which does not need to be specified, is HDR=Yes.
      singulars
      1. PO
      1. USLorenzo
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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