📄 vcg20.htm
字号:
<BR>
<LI>From Access, choose File | Save As to display the Save As dialog box. Select To an external File or Database and click the OK button. The Save Table...In... dialog box, shown in Figure 20.15, appears.
<BR>
<BR><B><A HREF="20vcg16.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg16.gif">Figure 20.15. Access 7's Save Table...In... dialog box.</A></B>
<BR>
<BR>
<LI>Select ODBC Databases from the Save as type list box and click the Export button to display the Export dialog box, shown in Figure 20.16. This dialog box confirms the table that will be exported. Click OK to display the SQL Data Sources dialog box, shown in Figure 20.17.
<BR>
<BR><B><A HREF="20vcg17.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg17.gif">Figure 20.16. Selecting the Customers table to export to an ODBC database.</A></B>
<BR>
<BR><B><A HREF="20vcg18.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg18.gif">Figure 20.17. Access's SQL Data Sources dialog box for ODBC datasources.</A></B>
<BR>
<BR>
<LI>Select the SQL Server datasource. If you don't have an SQL Server datasource configured, click the New button to add one.
<BR>
<BR>
<LI>Click the OK button to display the SQL Server Login dialog box, shown in Figure 20.18. Your own login user ID automatically appears in the Login ID text box, but you can change it if you want to. Enter your password if you aren't using the default system administrator user ID.
<BR>
<BR><B><A HREF="20vcg20.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg20.gif">Figure 20.18. The SQL Server driver's SQL Server Login dialog box.</A></B>
<BR>
<BR>
<LI>Click the OK button to begin the export process. Access's status bar displays the completion percentage of the process, so you can estimate how long it takes to export all of the records.
<BR>
<BR>
</OL>
<BR>
<A NAME="E69E258"></A>
<H4 ALIGN=CENTER>
<CENTER>
<FONT SIZE=4 COLOR="#FF0000"><B>Displaying the Result of the Export Operation with SQL Object Manager</B></FONT></CENTER></H4>
<BR>
<P>You can use the MS Query application to display the structure of the table you exported in the preceding section. MS Query, however, creates a Snapshot object of the entire contents of the table when you open your query. This can take an appreciable amount of time for a table with many records. Using Enterprise Manager to display the table structure is a faster approach.
<BR>
<P>Follow these steps to display the structure of the SQL Server version of one of NorthWind.MDB's tables you exported to the temp database and run a simple query to verify that its records are present:
<BR>
<OL>
<LI>Launch Enterprise Manager and connect to the server.
<BR>
<BR>
<LI>Select NorthWind in the Server Manager window and select the Objects folder under the NorthWind database. Open the Tables folder to display the exported tables, as shown in Figure 20.19.
<BR>
<BR><B><A HREF="20vcg21j.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg21j.gif">Figure 20.19. The Server Manager window showing all the objects, including the tables in the NorthWind database.</A></B>
<BR>
<BR>
<LI>Double-click one of the new tables you exported to display the structure of the table, as shown in Figure 20.20.
<BR>
<BR><B><A HREF="20vcg22j.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg22j.gif">Figure 20.20. Displaying the structure of an exported Access table in the Manage Tables dialog.</A></B>
<BR>
<BR>
<BLOCKQUOTE>
<BLOCKQUOTE>
<HR ALIGN=CENTER>
<BR>
<NOTE><B>NOTE</B>
<BR>
<BR>SQL Server, as well as other client-server RDBMSs, doesn't allow spaces in table or field names. The SQL Server ODBC driver substitutes underscores (_) for spaces and any other illegal punctuation in the names of Access tables or fields. Although field names in SQL Server databases traditionally use lowercase letters, capitalization of Access field names is preserved in the export operation.</NOTE>
<BR>
<HR ALIGN=CENTER>
</BLOCKQUOTE></BLOCKQUOTE>
<LI>Select the Query Analyzer to display the Query window. Enter a simple query, such as the one shown in Figure 20.21.
<BR>
<BR><B><A HREF="20vcg23j.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg23j.gif">Figure 20.21. Entering a simple SELECT query in SQL Administrator's Query window.</A></B>
<BR>
<BR>
<BLOCKQUOTE>
<BLOCKQUOTE>
<HR ALIGN=CENTER>
<BR>
<NOTE><B>NOTE</B>
<BR>
<BR>Table and field names are case-sensitive in SQL Server. Therefore, you need to type them exactly as they appear in the Column Name column of the Manage Tables window.</NOTE>
<BR>
<HR ALIGN=CENTER>
</BLOCKQUOTE></BLOCKQUOTE>
<LI>Click the Execute button to display the query result in the Results window for query #1, as shown in Figure 20.22.
<BR>
<BR>
</OL>
<P><B><A HREF="20vcg24j.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg24j.gif">Figure 20.22. The result of the SELECT query shown in Figure 20.21.</A></B>
<BR>
<BLOCKQUOTE>
<BLOCKQUOTE>
<HR ALIGN=CENTER>
<BR>
<NOTE><B>TIP</B>
<BR>
<BR>Use the SQL Server's BCP utilities to speed the import of data from large Access tables to SQL Server databases. Create copies of the Access tables using the clipboard and then delete all but a few records in the cloned tables. Export the table structure and the few records to the SQL Server database to create the new table structure and then delete the records in the SQL Server tables. Export the data from the Access tables to a comma-delimited text file. Then use SQL Object Manager's BCP utility to run a bulk copy operation to each SQL Server table. Michael J. Smith, a software engineer and DBA, reported in the article "SQL Server for Windows NT: A Case Study" in the May/June 1993 issue of <I>SQL Forum</I> that he was able to achieve a bulk copy rate of 530 records per second over a busy network running the BCP utility from a workstation. (Running BCP from the server reduces the bulk copy rate by about 50 percent.)</NOTE>
<BR>
<HR ALIGN=CENTER>
</BLOCKQUOTE></BLOCKQUOTE>
<BR>
<A NAME="E69E259"></A>
<H4 ALIGN=CENTER>
<CENTER>
<FONT SIZE=4 COLOR="#FF0000"><B>Adding an Index to an SQL Server Table with the SQL Object Manager</B></FONT></CENTER></H4>
<BR>
<P>When you export an Access table to a client-server database, Access doesn't create indexes on the client-server table. You need to use the SQL CREATE INDEX statement or the Enterprise Manager to manually add indexes to the client-server table. You can add one clustered or as many nonclustered indexes as you want to a table with Enterprise Manager. Clustered indexes, which physically place the pages of the table in the index order, greatly improve the performance of SQL Server databases. However, you gain a performance improvement with a clustered index only if you specify that the index must be unique. (Clustered indexes are not unique by default.)
<BR>
<P>To use Enterprise Manager to create a clustered primary key index on an imported table or any other table in an SQL Server database, follow these steps, which assume that the table you chose for the preceding example is open:
<BR>
<OL>
<LI>Choose Manage | Indexes to display the Manage Indexes dialog box for a new index, shown in Figure 20.23. Choose the table you want to index from the Table combo box and click the Build button.
<BR>
<BR><B><A HREF="20vcg25j.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg25j.gif">Figure 20.23. The dialog box to create a new index on an SQL Server table.</A></B>
<BR>
<BR>
<LI>Enter a name for the index in the New index name field, as shown in Figure 20.24.
<BR>
<BR><B><A HREF="20vcg26.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg26.gif">Figure 20.24. Specifying the name of an index to be added to an SQL Server table.</A></B>
<BR>
<BR>
<LI>If you're creating an index on the primary key, click the Clustered and Unique cells. Then choose the field(s) you want to index in order to create the primary key in the cells corresponding to the field names, as shown in Figure 20.25.
<BR>
<BR><B><A HREF="20vcg27.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg27.gif">Figure 20.25. Specifying the properties of an index on an SQL Server table.</A></B>
<BR>
<BR>
<LI>Click the Create button to create the new index. You will then be asked if you want to schedule the build for later or execute it, as shown in Figure 20.26.
<BR>
<BR><B><A HREF="20vcg28.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg28.gif">Figure 20.26. Confirming the addition of a new index to an SQL Server table.</A></B>
<BR>
<LI>When indexing is complete, close the Enterprise Manager application.
<BR>
<BR>
</OL>
<P>You can verify that the index is present and is recognized by the SQL Server ODBC driver by attaching the table to Access and then opening the table in design mode. Access assumes that a unique clustered index is the primary key index of an SQL Server table and indicates the key field(s) with the key symbol in the field selector button(s).
<BR>
<BR>
<A NAME="E68E105"></A>
<H3 ALIGN=CENTER>
<CENTER>
<FONT SIZE=5 COLOR="#FF0000"><B>Using ODBCTEST to Display the Capabilities of an ODBC Driver</B></FONT></CENTER></H3>
<BR>
<P>ODBCTEST is an application included with the Microsoft ODBC SDK that lets you inspect and use the low-level functions of the ODBC API. ODBCTEST is used primarily by developers of ODBC database drivers to debug and test the drivers' performance. If you intend to use different types of ODBC datasources or evaluate different drivers for a single data type, ODBCTEST lets you inspect and test each of the driver's functions.
<BR>
<BLOCKQUOTE>
<BLOCKQUOTE>
<HR ALIGN=CENTER>
<BR>
<NOTE><B>NOTE</B>
<BR>
<BR>ODBCTEST replaces the GATOR.EXE program that was part of the ODBC version 1.x SDK. There are only minor differences between ODBCTEST and GATOR.</NOTE>
<BR>
<HR ALIGN=CENTER>
</BLOCKQUOTE></BLOCKQUOTE>
<P>Figure 20.27 shows the ODBC Test window of ODBCTEST after you connect to the NorthWind database and then choose Connect | GetInfo All. The hdbc and hstmt combo boxes let you select the handle to the connected datasource(s) and statement(s) you execute either in the upper pane of the ODBC Test window or by choosing options from a menu.
<BR>
<P><B><A HREF="20vcg29.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg29.gif">Figure 20.27. The first few items returned by ODBCTEST's GetInfo All menu option.</A></B>
<BR>
<P>The GetInfo All menu choice returns more than you might want to know about the ODBC driver used to create the connection to the ODBC datasource. One of the most important features of the display provided by the GetInfo All command is the capability to determine the functions and data type conversions that the driver supports. You can use only those functions in your SQL statements that the ODBC driver translates into the appropriate syntax for the RDBMS in use. The documentation for supported functions that is supplied with ODBC drivers often doesn't include this information, and other times the information provided is incomplete. Figure 20.28 shows a few of the functions supported by version 1.02.3109 of the SQL Server ODBC driver.
<BR>
<P><B><A HREF="20vcg30.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg30.gif">Figure 20.28. ODBCTEST's list of some of the functions supported by the Microsoft ODBC driver for SQL Server.</A></B>
<BR>
<BLOCKQUOTE>
<BLOCKQUOTE>
<HR ALIGN=CENTER>
<BR>
<NOTE><B>TIP</B>
<BR>
<BR>ODBCTEST's File menu has a Print option, but it's always disabled. You can copy the information in the lower pane of the ODBCTEST's info window to the clipboard. If you want, you can then paste the data into Notepad in order to format and print the information so that it's easier to read.</NOTE>
<BR>
<HR ALIGN=CENTER>
</BLOCKQUOTE></BLOCKQUOTE>
<BR>
<A NAME="E68E106"></A>
<H3 ALIGN=CENTER>
<CENTER>
<FONT SIZE=5 COLOR="#FF0000"><B>Summary</B></FONT></CENTER></H3>
<BR>
<P>This chapter emphasized that creating Visual C++ front-end client-server applications doesn't differ substantially from designing similar applications that connect to conventional desktop databases with the Access database engine. The principal issues for the Visual C++ database developer are setting up the server database and the ODBC datasource(s) for the client-server back end. Thus, this chapter was devoted primarily to discussing the characteristics of a new, fast, and powerful client-server RDBMS, Microsoft SQL Server for Windows NT, and the Microsoft ODBC API.
<BR>
<P>The next chapter describes how to create mail-enabled Visual C++ applications that use the MAPI interface.
<BR>
<P ALIGN=CENTER>
<A HREF="vcg19.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/vcg19.htm" TARGET="_self"><IMG SRC="blanprev.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/blanprev.gif" WIDTH = 37 HEIGHT = 37 BORDER = 0 ALT="Previous Page"></A>
<A HREF="#I0" TARGET="_self"><IMG SRC="blantop.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/blantop.gif" WIDTH = 37 HEIGHT = 37 BORDER = 0 ALT="Page Top"></A>
<A HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/index.htm" TARGET="_self"><IMG SRC="blantoc.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/blantoc.gif" WIDTH = 37 HEIGHT = 37 BORDER = 0 ALT="TOC"></A>
<A HREF="vcg21.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/vcg21.htm" TARGET="_self"><IMG SRC="blannext.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/blannext.gif" WIDTH = 37 HEIGHT = 37 BORDER = 0 ALT="Next Page"></A>
</BODY></HTML>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -