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 |
|
boggyboy
Yak Posting Veteran
57 Posts |
 Posted - 2011-02-08 : 22:08:04
|
| I'm asking this question in the SQL database group so that I can perhaps understand why our SQL 2005 database programmer is so adamently in favor of removing MS Access from a very large reporting infrastructure. The situation is this... the customer has about 70 Excel workbooks. Each workbook has a MS Access database as its datasource. Each Access database has linked tables to a SQL 2000 database. There are some custom queries inside Access that pull data from the linked tables. Its the results of these custom queries that feed the Excel reports.Our DB programmer claims that "Access is bad" without really saying why. Consequently, Access must be systematically removed. This means he will re-write the queries of each Excel workbook to hit the SQL database directly. Why is Access so bad?? I don't get it.Nick W Saban |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-08 : 22:58:49
|
I have to agree with the programmer in that you should move toward having Excel hit the SQL databases directly.First, it removes a layer between source of truth and the report.Second, it allows the use of the data to be better monitored for performance and managed SQL side via Stored procedures, views or other objects in SQL server that can be optimized.Third, based on the second, changes to the database, and/or changes to business logic would only have to be changed in 1 place, rather than multiple access databases.Fourth, Access isn't "bad". It is a tool, just like excel, just like any other software, however the tool is more limited than SQL. Your DB programmer could better manage the reporting needs by centralizing the query optimization, data usage, and probably foresees a consolidation of business logic into the database design at some point. This is all for the better.It is important to understand that while Access is linking to the SQL server, that doesn't necessarily help with respect to the excel queries and potential problems.If the queries were managed within SQL server, the DB programmer could likely consolidate logic and produce a more reliable results.Additionally, having 70 workbooks hitting multiple Access databases, each with their own customized queries in both, can create vast inconsistencies in methodogies. By centralizing the usage of the data into SQL, the end result will be much more consistent, and much easier to manage when things change.What happens when business rules require a new column in a source table? All of the access databases have to be re-linked to obtain the new definition. The same would be true if a field changes (either by ordinal position, or by content). Such a change could either trickle down to the excel files and queries (causing failures, errors, or worse--wrong information).Bottom line, your DB programmer is mostly right in my opinion. Access is a user-friendly tool, yes, but it does have limits. You can only see 255 columns in access, and there are limits on it's growth (2 GB max) which requires additional overhead to manage (once it hits 2 gb, it is useless..can't even run a query "Invalid Argument" error). Linking directly via Excel to SQL server simplifies the process, simplifies maintenance and minimizes the risk of bad data flowing outward through them.Access does have limits...limits that can cause problems with usage of, reporting of, and analyzing of data.I think your DB programmer is seeing the big picture here, really, and trying to make the most of your SQL environment by leveraging it to simplify, consolidate and manage the downstream usage of the data as well as lower the risk of the worst case-incorrect results--being in the final result.Smart money is on letting him go down that path.The change in excel is minimal, and low risk. Much lower than the potential downstream impact of sticking Access in the middle.
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
boggyboy
Yak Posting Veteran
57 Posts |
Posted - 2011-02-09 : 00:58:26
|
| Thank you for the very clear explanation. I really appreciate it!!!Nick W Saban |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-02-09 : 19:22:43
|
| One of the major concerns with linked tables to SQL Server databases from Access is how the queries are created. In most cases, Access will process the query as a client-side cursor which effectively retrieves one row at a time from SQL Server. This is generally a performance killer, since it can take hours to pull the data from SQL Server.I have seen simple queries that shouldn't even take a second to process take more than 8 hours to process because of this.There are other issues as well. If the Access query joins between a linked table and a local table, Access will pull down the full table into Access from SQL Server before processing the query. If this is done against a small table, that might not be noticed - but against a table with a million rows it can be extremely poor performing. Note: a million rows in SQL Server is not even considered a large table now.Jeff |
|
|
|
louisse.ney
Starting Member
2 Posts |
Posted - 2011-03-14 : 22:48:49
|
hi!does anyone here knows how to delete the stock77 in mysql server 2005?thank you  |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2011-03-15 : 05:06:16
|
| Note1: mysql server 2005 <> ms sql server 2005Note2: the FAQ's for this site would alert you to the fact that this site is an MS SQL community, so your chances of getting an answer are slim unless your question includes a typo.www.dbforums.com may have more appropriate mysql specialists. |
|
|
|
|
|
|
|
|
Help me understand this will ya?