Microsoft Corporation
February 2000
Applies To: Microsoft® Office 2000, Microsoft Access 2000; Access 97; Microsoft SQL Server™ 7.0; SQL Server 6.5
Summary: This topic provides links to a wide variety of Microsoft Access and SQL Server integration resources. (15 printed pages)
Access 2000
ActiveX Data Objects
Upsizing to SQL Server from Access 2000
Microsoft Access Workflow Designer
Microsoft Data Engine
Office 2000
Access 97
Queries in Access 97
SQL Server 7.0
SQL Server 6.5
General Interoperability
Other SQL and Access Sites
Journals
User Groups
FAQs and General Information
In this MSDN Library article, Paul Litwin, Ken Getz, and Mike Gilbert discuss the ANSI SQL-92 extensions, highlighting the differences between standard Microsoft® Access SQL and the new Jet 4.0 extensions.
http://support.microsoft.com/support/kb/articles/Q232/3/79.ASP
The example in this Knowledge Base article demonstrates how to create a temporary table through stored procedures, insert data into the table, and display data in the temporary table in the user interface.
http://support.microsoft.com/support/SQL/Content/inprodhlp/idh_ac_security.asp
This Knowledge Base article provides an overview of SQL Server™ security for Access 2000.
http://support.microsoft.com/support/kb/articles/Q224/5/29.asp
As explained in this Knowledge Base article, when you import a table from a Microsoft Access project into an Access database, and then import that table from the Access database into an Access project, some data types change.
This MSDN article by Rick Dobson starts with a brief overview of the new SQL Server interoperability functionality, then discusses two of the new features, the new .adp file type and the Microsoft Data Engine (MSDE), in some detail.
http://support.microsoft.com/support/kb/articles/Q233/3/92.ASP
This Knowledge Base article provides a code sample that uses the Script method of SQL-DMO to generate a Transact-SQL command batch, which in turn creates a variety of objects such as tables, views, and stored procedures.
http://support.microsoft.com/support/kb/articles/Q209/1/23.ASP
This Knowledge Base article describes how to achieve a compromise between pass-through and Access queries by creating a view on the server and then linking it.
http://support.microsoft.com/support/kb/articles/Q209/5/23.ASP
The INSTCAT.SQL file (included in SQL Server 6.5) is used with the SQL Server ODBC driver to create stored procedures for use with SQL Server version 6.5. This Knowledge Base article describes the SQL stored procedure that you can use to query for the version number of the INSTCAT.SQL file that was run against the SQL Server computer.
http://support.microsoft.com/support/kb/articles/Q230/1/70.ASP
This Knowledge Base article discusses how to programmatically start the dialog boxes you need to administer SQL Server replication, administer SQL Server security, and back up your SQL Server database through the user interface.
http://support.microsoft.com/support/kb/articles/Q209/1/16.ASP
This Knowledge Base article shows you how to use an SQL pass-through query for a form's record source.
http://support.microsoft.com/support/kb/articles/Q234/3/43.ASP
When you create a database diagram in a Microsoft Access project, it is not stored on SQL Server or Microsoft Data Engine (MSDE) as an object (as a table, a view, or a stored procedure would be). To secure database diagrams in an Access project, follow the steps in this Knowledge Base article.
http://support.microsoft.com/support/ServiceWare/Access/Acc2000/EA43EI2NY.ASP
This reference table lists field names and data types translated between SQL Server fields and a Microsoft Access table.
http://support.microsoft.com/support/ServiceWare/Access/Acc2000/EA46BVV4K.ASP
This Knowledge Base article describes how to use a stored procedure in Access 2000 to log on to an ODBC source.
This article from the MSDN Library describes how to use ActiveX® Data Objects (ADO) to write compact and scalable scripts for connecting to OLE DB–compliant data sources, such as databases, spreadsheets, sequential data files, or e-mail directories.
http://support.microsoft.com/support/ServiceWare/Access/Acc2000/EA46LE0N7.ASP
This Knowledge Base article describes how to use ADO to dump inactive Access 2000 data in a SQL Server transaction log.
http://support.microsoft.com/support/ServiceWare/Access/Acc2000/EA46LOUAV.ASP
This Knowledge Base article shows you how to create a function that uses ADO connections through a data source name (DSN) on the workstation.
This MSDN Library article by Microsoft Support Engineer Russell Christopher shows you how to use the Access 2000 Upsizing Tools. The quickest and easiest way to move data residing in an Access database to Microsoft SQL Server version 7.0 or the Microsoft Data Engine (MSDE) is by using the Upsizing Tools, which are built directly into Access 2000. These tools allow you to migrate tables and common queries in your Access (Jet) database to SQL Server or MSDE.
http://support.microsoft.com/support/kb/articles/Q237/9/80.ASP
This Knowledge Base article provides an overview of converting an Access database to SQL Server by using the Upsizing Wizard.
http://msdn.microsoft.com/community/peerjournal/g071898d.asp
This article in the MSDN Peer Journal explains how to decide which tables to export to SQL Server and which to keep in the local Access database; how to choose table and field names that are compatible with SQL Server; how to avoid name-length problems when the Upsize tool creates the SQL Server default values; and how to preserve the referential integrity in the new SQL Server database.
This article from the MSDN Library provides tips for improving performance of a team solution created using the Microsoft Access Workflow Designer for SQL Server, a tool provided by Microsoft Office Developer.
http://msdn.microsoft.com/officedev/technical/articles/hottopics/1099.asp
On this page we've assembled a set of articles designed to help you use the Access Workflow Designer to build team workflow solutions.
In this MSDN article, James Fitzgerald of Clarity Consulting, Inc., walks us through the migration of an existing sample Access database—using the Jet engine—to an Access and MSDE client-server database application, and describes installing a complete version of a client-server Access application. He also discusses various options for accessing data within MSDE databases, including Microsoft's ActiveX Data Objects (ADO) and Data Access Objects (DAO) technologies. A reference section comparing embedded database technologies is included at the end of the article.
www.microsoft.com/office/access/MSDtaEng.htm
This white paper on the Microsoft Office Web site gives background information about the advantages of using Access 2000, plus helps you choose the best engine for a database by comparing Microsoft Jet 4.0 and Microsoft Data Engine (MSDE) in terms of enterprise requirements, usage patterns, and features.
This MSDN Library article discusses how to use the Microsoft Data Engine to build scalable client/server database applications that easily migrate to Microsoft SQL Server.
www.microsoft.com/SQL/productinfo/msdejet.htm
This white paper on the Microsoft SQL Server Web site familiarizes readers with Access, discusses enhancements to Jet, and introduces MSDE functionality. It will help organizations determine which option best meets their current and future information management needs.
http://support.microsoft.com/support/kb/articles/Q199/3/95.ASP
This Knowledge Base article describes how to use the Sqlstp.log file to see whether you are running Microsoft Data Engine (MSDE) or Microsoft SQL Server 7.0.
www.microsoft.com/SQL/productinfo/msde.htm
This white paper on the Microsoft SQL Server Web site outlines installing Office MSDE, creating a new Access database solution, and distributing the solution on computers across the enterprise. The paper also introduces packaging any MSDE-based solution by using the Microsoft Visual Basic® for Applications Package and Deployment Wizard.
www.microsoft.com/SQL/productinfo/officeintegration.htm
View this page of links on the Microsoft SQL Server Web site to get their take on integrating Office 2000 with SQL Server 7.0.
www.microsoft.com/SQL/productinfo/office2000.htm
This white paper on the Microsoft SQL Server product site explains how you can benefit by pairing SQL Server 7.0 with Office 2000.
www.microsoft.com/Office/ORK/2000/One/10ct_4.htm
This article on the Microsoft Office Web site provides an overview of the database server support in Access 2000, Excel 2000, and Office Web Components for data stored by using SQL Server and other database servers.
www.microsoft.com/Office/ORK/2000/One/10t3_1.htm
This article on the Microsoft Office Web site describes how Office 2000 interacts with Microsoft Jet, Microsoft Data Engine (MSDE), and Microsoft SQL Server version 7.0.
www.microsoft.com/Office/ORK/2000/One/10t3_3.htm
This article on the Microsoft Office Web site describes how Office 2000 supports Universal Data Access while continuing to support a broad variety of other data formats and data access technologies.
www.microsoft.com/products/developer/officedeveloper/Access/ProdInfo/Aut97dat.htm
Microsoft Access Upsizing Tools allow you to create your solution in Access and "upsize" it by moving your database to SQL Server. Follow this link to the old Office Developer Web Forum to download the tools and get more information.
This article by Microsoft Software Design Engineer Steve Kirk discusses the issues involved with converting the Duwamish Books database from Access 97 to SQL Server.
http://support.microsoft.com/support/ServiceWare/Office/Mso40/E9JA5VANP.ASP
This Knowledge Base article lists the steps needed to set up Access for use with SQL databases.
http://support.microsoft.com/support/kb/articles/Q128/8/08.asp
This Knowledge Base article lists tips to help you optimize performance when you use Open Database Connectivity (ODBC) data sources with Microsoft Access.
This article by Jamie Beidleman from the December 1997 issue of Smart Access discusses using Access as a database design tool for SQL Server applications.
http://support.microsoft.com/support/kb/articles/Q101/0/84.asp
This Knowledge Base article describes how to store SQL database login IDs and passwords locally.
http://support.microsoft.com/support/kb/articles/Q180/3/68.asp
This Knowledge Base article demonstrates how to access and update large text fields (Binary Large Objects/BLOBS) by using ActiveX Data Objects (ADO).
http://support.microsoft.com/support/kb/articles/Q154/0/11.asp
This Knowledge Base article shows you how to use Automation to list the objects that exist in a Microsoft SQL Server version 6.0 or 6.5 database.
http://support.microsoft.com/support/kb/articles/Q102/4/37.asp
This article lists some techniques that you can use to determine indirect causes of error messages when using linked (attached) SQL tables.
http://support.microsoft.com/support/kb/articles/Q147/1/92.asp
This Knowledge Base article lists Microsoft SQL Server and ORACLE ODBC drivers that you can use with various versions of Microsoft Access and Microsoft Windows. In addition, the article lists the sources from which you can obtain the ODBC drivers.
http://support.microsoft.com/support/kb/ARTICLES/Q99/3/21.asp
This Knowledge Base article outlines tips on how to ensure that queries against attached SQL database tables are performed on the server.
http://support.microsoft.com/support/kb/articles/Q112/1/08.asp
This Knowledge Base article shows you how to create an SQL pass-through (SPT) query by using Visual Basic for Applications (VBA) code.
http://support.microsoft.com/support/kb/articles/Q124/3/91.asp
This Knowledge Base article provides sample code that you can use to call and run a temporary SQL pass-through query.
http://support.microsoft.com/support/kb/articles/Q131/5/34.asp
This Knowledge Base article demonstrates two sample user-defined VBA functions that you can use to pass parameters to an SQL pass-through query.
http://support.microsoft.com/support/kb/articles/Q153/7/47.asp
This Knowledge Base article shows you two methods that you can use to create queries that list only the top N items per group. By using either one of these methods, you can create a query that displays only the top five salespeople for each region.
http://support.microsoft.com/support/ServiceWare/Access/ACC97/EA3W8ENTK.ASP
This Knowledge Base article describes how to use three queries (a left outer join query, a restricted right outer join query, and a union query of two previous queries) to simulate a full outer join.
http://support.microsoft.com/support/default.asp?PR=sql&FR=1&SD=SO&
This page on the Microsoft Personal Online Support provides links to a variety of SQL resources, including a download of the SQL Server 7.0 Books Online product documentation.
This online book from the MSDN Library provides essential documentation on using, administering, and developing applications for Microsoft SQL Server.
http://officeupdate.microsoft.com/2000/downloadDetails/Sql70sp1.htm
This page on the Microsoft Office Update site provides a download of Service Pack 1 for SQL Server 7.0 and MSDE 1.0.
http://support.microsoft.com/support/sql/70faq.asp
This Knowledge Base article provides links to other articles that address frequently asked questions regarding SQL Server 7.0.
www.microsoft.com/TechNet/sql/support.asp
This page on the Microsoft TechNet Web site provides links to support and troubleshooting resources for Microsoft SQL Server 7.0 and 6.5.
http://support.microsoft.com/support/kb/articles/Q216/4/15.ASP
This Knowledge Base article describes how to configure Microsoft SQL Server so that it can be accessed by using the Server Proxy features of Microsoft Proxy Server.
This paper from the MSDN Library introduces the logical three-tier development model and then describes four physical implementations of that model. This is followed by a discussion of database access styles, choosing and optimizing your interface, and tips for leveraging built-in SQL Server features.
http://support.microsoft.com/support/kb/articles/Q197/9/16.ASP
AdoGUIDz.exe is a sample project available for download from the Microsoft Software Library that demonstrates using the globally unique identifier (GUID) data type with Microsoft Access, SQL 6.5, and SQL 7.0. The sample code may be particularly helpful if you are attempting to manipulate GUIDs with ODBC versions 3.51 and earlier, since those versions of ODBC do not support a native GUID data type.
This article from the MSDN Library provides you with a preview of Microsoft English Query (MSEQ) 7.5, a product that sits on top of some SQL-based OLE DB providers (such as Microsoft SQL Server, Microsoft Access, and Oracle) and lets you model queries in plain English instead of complex SQL commands. MSEQ integrates well with SQL Server (both 6.5 and 7.0) and Microsoft Visual InterDev®.
www.microsoft.com/SQL/interopmigrate/migrateoverview.htm
Learn about the benefits of migrating to Microsoft SQL Server 7.0 and the fundamentals of planning a migration strategy.
This MSDN Library technical article describes how to migrate tables and queries into SQL Server scripts, views, and stored procedures. It also outlines methods to ensure that applications, once in place, are tuned to run efficiently.
http://support.microsoft.com/support/kb/articles/q233/3/12.asp
This Knowledge Base article describes how to automate an unattended (or silent) installation of SQL Server 7.0 or MSDE 1.0 by using an .iss file that records the responses and selections that would be provided by a user during an interactive installation.
www.microsoft.com/SQL/productinfo/licensesummary.htm
This page on the Microsoft SQL Server Web site provides information in the form of summary tables about the licensing requirements for SQL Server, as well as a link to the pricing and licensing page.
http://support.microsoft.com/support/sql/content/faq/default.asp
This Knowledge Base article provides links to other articles that address frequently asked questions regarding SQL Server 6.5.
http://support.microsoft.com/support/kb/articles/Q135/6/84.asp
This Knowledge Base article contains abstracts of the Frequently Asked Questions received by Microsoft's SQL Server support engineers.
http://support.microsoft.com/support/kb/articles/Q169/3/77.asp
This Knowledge Base article describes how to establish connections to SQL Server within an ASP script by using ActiveX Data Objects (ADO), while taking advantage of the connection pooling feature of ODBC 3.0.
http://support.microsoft.com/support/kb/articles/Q152/0/35.asp
This Knowledge Base article describes use of the IDENTITY_INSERT option when you insert or append data from Microsoft Access to a Microsoft SQL Server table containing an identity column.
http://support.microsoft.com/support/kb/articles/Q150/3/22.ASP
This Knowledge Base article presents examples of nested outer joins that use the SQL Server and Access drivers.
http://support.microsoft.com/support/kb/articles/Q137/6/33.asp
This Knowledge Base article discusses the steps you should take to address the most common causes of Microsoft Access conformance errors raised from the Microsoft SQL Server ODBC driver.
www.microsoft.com/Data/isg/default.htm
This page features companies who are developing innovative tools for use with the Microsoft Data Access Technologies, which help a wide variety of customers access all types of data more efficiently.
http://support.microsoft.com/support/odbc/FAQ/all.asp
This Knowledge Base article addresses frequently asked questions regarding Open Database Connectivity.
Note These links lead to non-Microsoft sites that contain information about developing Microsoft Office solutions.
www.dbpd.com/vault/index.shtml
www.pinpub.com/sqlpro/home.htm
www.geocities.com/~dsnydersql/sqllinks.htm
http://web2.cc.utexas.edu/cc/dbms/sw/mssql/
www.swynk.com/faq/sql/sqlserverfaq.asp