Excel as front end and SQL Server as back end


R

ryguy7272

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--
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top