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