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 |
|
pmak
Starting Member
41 Posts |
 Posted - 2004-06-21 : 23:25:23
|
| I am a newbie in SQL development. My question is how to create a table in SQL 2000 which can be real time linked to a Excel worksheet, so that user can edit, add or delete data either at the Excel worksheet or directly to the SQL table. All the changes can be refreshed instantly on either locations. Basically I want the SQL 2000 as the back end/data storage of an application, the end user will use Excel for data entry or they can build pivot table for data analysis, also I will build an Access front end for data reporting only. I know how to link SQL 2000 to Access using ODBC but I have not had a clue on how to link SQL 2000 to Excel. |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-06-22 : 00:32:04
|
| You can link either way.You can use MS Query or even a VBA macro to read data from SQL Server via an ODBC/OLEDB connection.You can also link Excel to SQL Server using Linked Tables.I think you're asking for trouble using Excel as a data entry app, as you will need to manually code all the data validation and reads/writes yourself.I know you can use DDE to communicate b/w Access and Excel. It's not too difficult to do - I did a complex one a while ago for doing actuarial calculations.Check out the Access doco for more info on DDE. |
 |
|
|
pmak
Starting Member
41 Posts |
Posted - 2004-06-22 : 00:41:58
|
| Hi Timmy:Thank you for your reply. Please would you give me a link to a sample of using DDE to communicate with Excel. By the way I typed in the "Access doco" and hope to find more information regarding this, but I could not find any in the web site. What is "Access doco"? Thanks for your advice. |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-06-22 : 00:51:32
|
| Short for Access documentation.Open the Access help file and do a search on DDE. There's a number of examples there.NB: Be aware that this isn't a 'live' link as you might expect when opening a table in Access. |
|
|
|
pmak
Starting Member
41 Posts |
Posted - 2004-06-22 : 00:54:28
|
| By the way how to create a "LINK TABLE" in EXCEL so that SQL data can be updated using Excel just like in Access. |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-06-22 : 01:02:34
|
| I don't think you can.You can populate a spreadsheet using MS Query (Tools - Get External Data), but it won't update the database when you make changes. That's what Access is for. |
|
|
|
|
|
|
Real time link from SQL to Excel