Excel - SQL Server 2000

• Phil Davy

  Old Hand

  Points: 382

  More actions

  June 27, 2006 at 10:49 am

  #169319

  Dear all

  Basically I’ve got an excel spreadsheet and a SQL server table.

  I want to be able to retrieve a record from SQL into excel.

  Then the user should be able to modify a record and pump it back into SQL

  Also I’d need to get it to create new records or delete old records.

  The point being that 10 separate excel insurance ratingmodels select/update/insert data from/to the sql table.

  I have to use SQL as looking at 2000 records with 500 columns!  With up to 10 concurrent users.

  Am a bit stuck what to do, was thinking of ADO? But not sure.

  I have zero experience of ADO , good experience of SQL server and some experience of VBA, and want to do it as easily as possible, maybe using DTS if relevant.

  Don’t suppose anyone has any ideas how I could do this - any hyperlinks much appreciated if ok?!

  Many thanks

  Phil

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  June 28, 2006 at 1:41 am

  #646290

  Microsoft Excel, prior to version 2007, does not support 500 columns. Max is 256 columns for older versions and 1024 columns for Excel 2007.

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• Gosta Munktell

  SSCertifiable

  Points: 6004

  More actions

  June 28, 2006 at 5:48 am

  #646334

  Dear Phil,

  It should be possible to accomplish what you describe but your description is to vague for an "exact" advise.

  I have made many applications where a database like SQL-server and Excel "share" data.

  I use VB or VBA and ADO (not DTS for this purpose). There is a lot to find on Internet an example is

  How To Use ADO with Excel Data from Visual Basic or VBA  Microsoft http://support.microsoft.com/kb/257819/EN-US/

  or use a forum like EXCEL-L@PEACH.EASE.LSOFT.COM to get more examples or

  http://www.excelkb.com/article.aspx?id=10008 

   

  Gosta Munktell

  Uppsala Sweden