Home » Visual StudioRSS

Excel as front end and SQL Server as back end

I read some documentation on the web today about using Excel as front end and
SQL Server as back end.

Actually, this link was pretty good:
http://support.microsoft.com/kb/321686

This is pretty good too: http://bytes.com/topic/sql-server/answers/486783-updating-sql-server-Table-using-excel

This is very good; gets pretty complicated though:
http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/

I’m still pretty confused about how it all works. I found a sample of VBA
online that lets me import a couple columns from a Table:
Sub Import()
' Declare the QueryTable object
Dim qt As QueryTable
' Set up the SQL Statement
sqlstring = "select LastName, FirstName from Employees"
' Set up the connection string, reference an ODBC connection
connstring = _
"ODBC;DSN=Northwind;UID=;PWD=;Database=Northwind"
' Now implement the connection, run the Query, and add
' the results to the spreadsheet starting at row A1
With ActiveSheet.QueryTables.Add(Connection:=connstring,
Destination:=Range("A1"), Sql:=sqlstring)
.Refresh
End With
End Sub

That’s pretty slick, actually! However, I’m thinking I need to use Excel to
LINK to SQL Server, not COPY or IMPORT. Basically, I’m trying to find an
easy way to use Excel as a front end interface so that a user may make
changes in Excel and save all changes back to SQL Server? This should most
likely update a Query and have this Query update several Tables, I would
think. I believe this would be some type of ‘append Query’ or I would
somehow append new records. I do a lot of work in MS Access and if I was
using an Access Form, I’d connect the Form to a Query, make changes to
records in the Form, and save the changes back to the Query, which
subsequently updates the data in the underlying Tables. How can I do
something similar, but use Excel as a front end and SQL Server as a back end?

I’m using Excel 2007 and SQL Server 2008 Express.

I’d definitely appreciate any/all help with this! I guess if I could just
see some simple examples, step by step, and get something working, I could
probably figure out the rest.

Thanks!
Ryan--
 

4 Answers Found

 

Answer 1

Since you are using Excel 2007, so try this, Click Menu "Data->From Other Sources->From SQL Server", then just follow the wizard and fill the information required.
 

Answer 2

Right, that's very easy.  However, I need to link  to SQL Server, not IMPORT from SQL Server.  Basically, I'd like to open an Excel workbook that is linked to SQL Server, make a few changes in the spreadsheet, and then run  some code and see the updates in SQL Server.  I know it's possibly because I've seen it done before, but I don't have the code to do it, so I'm looking for some sample  code to get started.

Thanks!
Ryan--
 

Answer 3

Hi Ryan!

I'm looking for the same VBA code based solution. I've also bumped to the links above during my web  research. To me this problem should be somewhat standartized as the solution itself is not that difficult to implement technically, but the problem is in customization.

Did you make any progress with your project? If so please let me know the outcome.

Thanks in advance,

Sergey

 

Answer 4

Hello,

Were either of you able to get this working?

Thanks!

Tom

 
 
 
 
 
 

<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter