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 |
|
LearningSQL2
Starting Member
10 Posts |
 Posted - 2011-07-28 : 11:19:04
|
| Does anyone know how to rig up an SQL server on the MS 2008 Express version that shares a continuious link with an excel sheet so that the SQL database is triggered to automatically perform querries whenever the excel table is updated? I cannot find information about this anywhere: all of the triggers and pre-strored processes that I've read about require the user to get onto SQL to use them. Is it even possible to have SQL perform this automatically?As a bit of background, I am working on an ambitious (for a begginer) project to create an SQL database that automatically recieves input from a large, and frequently updated MS excel customer database, interprets the data, and then exports that data to a MS publisher document for customer consumption. I am not looking for answers, just direction and advice. Many thanks in advance for any useful guidance. -MTD |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-28 : 11:33:22
|
| What's the purpose of having it imported in SQL Server? Why not just have an Excel macro update the Publisher document?Next question after that is, why have Excel as the data source? Why not instead move the data to SQL Server and use Excel as a front-end to edit it?Either scenario will be a lot easier to implement. |
 |
|
|
LearningSQL2
Starting Member
10 Posts |
Posted - 2011-07-28 : 12:22:05
|
| Sorry about the duplicate post: I thought including it on several forums might get more answers from members who only read one or two. If duplicate-posting is bad manners on this forum, I appologize and will not do it again. To answer your questions:The SQL server will be eventually needed to make the database accesible to customers on-line. (Right now we're just trying to get it to do what the Excel macro update already does. However, once we start trying to have customers access the database from the web, the thinking goes that having the coding already in place will save some time.)My lack of familiarity with SQL terminology and nomenclature may have muddled my meaning, but your second question described what I am trying to do: host the database on SQL and have Excel as a front-end to edit or update it, in order to idiot-proof the database as much as possible. As part of this process, SQL will need to analyize the updated information. It will recieve numeric values from Excel and then decide if they are too low, too high, or within a given range. This information will then be sent to, and displayed on, the publisher document. What I'm trying to figure out is how to make SQL automatically perform this analyis, because everything I've read seems to indicate that the DBA has to input commands into SQL every time he wants export the new data to Publisher. I want to try to do this automatically instead. Are there any existing threads, snippets of code, or really good on-line articles about the subject you could point me towards? Thanks again for your advice. It's made me re-think what I'm trying to use SQL to do. Maybe there is a simpler way to implement my plan.-MTD |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-28 : 12:34:21
|
quote:
I thought including it on several forums might get more answers from members who only read one or two
We all pretty much read everything.  It's just easier to answer in one place.I've done Excel front-ends to SQL Server in the past, and while it can work it can get pretty complicated. It's much easier to do that in MS Access with linked tables, if that's an option for you.As far as analyzing/validating data, you can certainly do that in SQL Server, but basic validation is better done in Excel. For example, if a range is fixed (1-100) then you can put a data validation constraint on the cells in Excel. If the range has a formula behind it, and it's not too complicated, you could also use Excel's validation. If it's really complicated, like having to check several different tables in SQL Server, then you'd want to use Excel macros to call a SQL Server stored procedure, passing the cell's value as a parameter, and returns whether the value is valid or not. You can then have the macro change or erase that value and prompt them accordingly. So while you're still passing commands to SQL Server, the macro automates most of the process. The only real programming question is how to trigger the SQL call; for example, a button they have to click to validate. |
|
|
|
|
|
|
|
|
Can an SQL server beriggedto work automatically?