Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
 Posted - 2002-08-15 : 12:20:07
|
| I've had success querying Access databases using DAO/vbscript from within Excel and I'd now like to move to querying my SQL Server 7.0 databases directly in the same fashion.The big Question: can this be done simply by modifying the code I've been using for Access, possibly just by referencing my SQL db via its System DSN and then making the necessary changes for table names, fields, etc.?Microsoft doesn't seem to provide much info on direct Excel-to-SQL queries, unless I've just been searching poorly. Anyone have any tips, code snippets, links to resources they'd like to share?Thanks. |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-08-15 : 12:38:49
|
| You can also use the Get External Data tool in Excel, and even link a spreadsheet (or portions of it) to your SQL Server tables. Once it's set up you can refresh it manually, or with a little VB code, automatically every time the workbook is opened. I wish I had known about it several years ago, it is a lot easier than DAO and ADO (though not as flexible, but still good enough)Also check out the CopyFromRecordset method if you don't already know about it, it will make life a lot easier.And use ADO instead of DAO when querying SQL Server, DAO BLOWS MOOSE with ODBC data sources. |
 |
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2002-08-20 : 11:44:31
|
| Yes, robvolk, Get External Data is fabulous. I'm using it to directly load Views from SQL 7.0. This is all I need. Take care of the queries on the SQL Server end and just load the View in Excel.thx |
|
|
|
|
|
|
|
|
query SQL server directly from Excel?