Data Source: Programmatically Creating a Table in an ODBC Data Source

|  Overview |  How Do I |  FAQ |  Sample |  |  ODBC Driver List

This article explains how to create a table for your data source, using the ExecuteSQL member function of class CDatabase, passing the function a string that contains a CREATE TABLE SQL statement.

For general information about ODBC data sources in MFC, see the article Data Source (ODBC). The article Data Source: Programmatically Configuring an ODBC Data Source describes creating data sources.

Once you have the data source established, you can easily create tables using the ExecuteSQL member function and the CREATE TABLE SQL statement. For example, if you had a CDatabase object called myDB, you could use the following MFC code to create a table:

myDB.ExecuteSQL("CREATE TABLE OFFICES (OfficeID TEXT(4)" ",
                         OfficeName TEXT(10))");

The code above creates a table called “OFFICES” in the Microsoft Access data source connection maintained by myDB; the table contains two fields “OfficeID” and “OfficeName.” For more information about creating tables as well as primary keys and indexes for them, see Appendix C in the ODBC Programmer’s Reference.

****Note   ****The field types specified in the CREATE TABLE SQL statement may vary according to the ODBC driver that you are using. The Microsoft Query program (distributed with Visual C++ 1.5) is one way to discover what field types are available for a data source. In MS Query, select File, choose Table_Definition, select a table from a data source, and look at the type shown in the “Type” combo box. Appendix C in the ODBC Programmer’s Reference describes the supported SQL syntax. SQL syntax also exists to create indexes. The Programmer’s Reference is in the ODBC SDK on the MSDN Library CD.