| Author |
Topic |
|
detlion1643
Yak Posting Veteran
67 Posts |
 Posted - 2010-02-04 : 12:21:51
|
| I have about 50+ excel sheets that are linked to access (so when excel sheets are updated, the queries are run on updated data). I have now come to the point where my access queries are getting too complex and error out in access.Is there a way in sql to link to excel, so when the excel sheets are updated, the sql data is updated as well. I cannot just import, as it would take like 10-20+ imports of data a day. |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2010-02-04 : 12:37:15
|
| You could come into the 21st century and turn your excel sheets into a web application.Otherwise, yes there is a way to do that. Not recommended though.http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
 |
|
|
detlion1643
Yak Posting Veteran
67 Posts |
Posted - 2010-02-04 : 12:47:34
|
| I actually know nothing of web applications or development... I am mostly stuck to coding vb.net/access/excel at work. I was just hoping there was a way to link the sheets like there is in access. Oh well, guess I'm stuck for now until I have time to learn web applications. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-02-04 : 12:51:37
|
quote:
Originally posted by detlion1643
I actually know nothing of web applications or development... I am mostly stuck to coding vb.net/access/excel at work. I was just hoping there was a way to link the sheets like there is in access. Oh well, guess I'm stuck for now until I have time to learn web applications.
Maybe u can create a windows service in vb.net that imports the data from excel sheet after a specific period of time.PBUH |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
detlion1643
Yak Posting Veteran
67 Posts |
Posted - 2010-02-04 : 13:05:43
|
| Yes, using SQL 2008 Express at the moment for testing if it can be done. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2010-02-04 : 13:14:31
|
| You can link to SQL Server using Data\GetExternalData\NewDatabaseQuery. You can import the data into Excel that way, you can wrap a pivot table around a SQl table Data\PivotTable\ExternalDataSource. You can then let the user hit a refresh button or set things to refresh on open. I'm in the same boat you are, but they tell me that we may be upgrading to Excel 2003 some time soon!JimEveryday I learn something that somebody else already knew |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
detlion1643
Yak Posting Veteran
67 Posts |
Posted - 2010-02-04 : 13:53:24
|
| I just messed with linking the Access server into SQL (as linked server), but only a couple tables show. The linked excel sheets into Access do not show :(... Anyways, the sheets can be worked on at anytime, so I thought linking would be the best option. Otherwise, I would need to import 50+ sheets multiple times a day with a SQL server job (is it time consuming?) |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
detlion1643
Yak Posting Veteran
67 Posts |
Posted - 2010-02-04 : 14:45:55
|
| Ya, i've been trying to look for a way to do that (create a link, not an import) but have not succeeded so far. I guess I'm done trying to link to sql, thanks anyways guys... |
|
|
|
|
access to sql