Previous editions of Microsoft Office have provided some degree of connectivity between Office applications and third-party data sources. There has also been a fair degree of integration with SQL Server — which, I guess, counts as a first-party data source. But Office 2007 introduces some new features to integrate Office all the more tightly with SQL Server.
Those not well-versed in Microsoft Access turn to it as a kind of SQL Server to do quick-and-dirty, single-user database programming. Before the advent of SQL Server 2005 Express Edition, that was about the only way to do such a thing without the expense of a full SQL Server installation. Most organizations that had an Office license had Access as part of the bargain. Databases built in Access alone could be deemed heavily self-limiting; The possibility was there for it to only allow one concurrent user at a time per database and not have the more powerful data analysis functions of SQL Server. The problem may lie in the fact that it is possible to create some very badly-designed Access databases; Those that were intended to support multiple users were not put together by people who really understood how to do that. The sight of too many bad Access implementatons has turned some people off.
The generic answer suggested to them was to upgrade to SQL Server as the backend, rather than to use programming approaches for Access that make multi-user scenarios possible.
Using Access as a front end for SQL Server lets you take advantage of the SQL Server data engine, and use Access forms and reports with it.
Access 2007 has two ways you can connect to SQL Server data: linking to SQL Server via an MDB file or ACCDB file (the new Access 2007 file format, which takes the place of the MDB file extension), or by creating an Access Data Project (ADP).
ADPs are OLE documents that contain front-end presentation items (forms, reports, VBA code, etc.), which are connected to tables and queries in SQL Server. AD...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
Ps are a legacy technology and have a default limit to the number of records they can return for a given query. The documents are being phased out in favor of using MDB or ACCDB files. Among other things, these preferred formats can link to multiple data sources at once and have other features that aren't available through ADPs. Access 2007 will still work with ADPs they are, but no new features have been added to the mix. There is a general sense that ADPs will not be supported at all in the next edition of Office.
Microsoft is also preparing to offer add-on tools for Office in order to enhance SQL Server integration in the future. One such tool, SQL Server 2005 Data Mining Add-ins for Office 2007, is currently only in Community Technology Preview (i.e., beta) form, but you can download it and try it out. The package adds data mining and table-analysis tools for Excel documents that are back-ended with SQL Server data, and a tool for Visio 2007 allows you to render mining models as a Visio drawing.
Another new Access 2007 feature is the migration function that allows you to export an Access database to SQL Server. Both the full version of SQL Server and SQL Server Desktop Edition are supported; the only limitations are whatever limitations are inherent in the edition of SQL Server you're connecting to. This function is helpful if you have more Access experience than you do SQL Server. It's a bypass option, so to speak, for moving a database into SQL Server as quickly as possible without having to learn specifically about SQL Server's migration tools. In the long run, you'll probably need some more SQL Server experience under your belt, so don't think of the Office 2007 tools as substitutes for knowing SQL Server intimately.
On a final note, if you want to gain experience with Office 2007 and SQL Server 2005, both products come in time-limited trial editions. SQL Server 2005 Express Edition itself is free, and although it does not have the full set of SQL Server features, it has many that would be important for people just getting their feet wet.
ABOUT THE AUTHOR:
Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
Copyright 2007 TechTarget
More on SearchSQLServer.com
Business intelligence features in SQL Server 2005
How to reuse datasets with Reporting Services
Reporting Services performance analysis and tuning
