Microsoft Access 2007 integration with SQL Server
Serdar Yegulalp, Contributor
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
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
Dig Deeper
-
People who read this also read...
-
This was first published in January 2007
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. ADPs 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 has been writing about computers and information technology for
more than 15 years for a variety of publications, including InformationWeek and Windows
Magazine.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation