How To Create Tables with Foundation Database Classes

This article was previously published under Q110508
This article has been archived. It is offered "as is" and will no longer be updated.
Summary
Microsoft Foundation Classes (MFC) version 2.5 and later use the OpenDatabase Connectivity (ODBC) API (application programming interface) toaccess and manipulate data. Before a table can be created, ODBC requiresthat a "data source" be configured to allow access to data. A data sourceis a set of data that can be located in a variety of databases (forexample, FoxPro, Access, SQL Server, and so forth) in various locations(for example, local or network). Once a data source is configured/named,the programmer only needs to specify the name of the data source to connectto the data. To create a table for the data source, use theCDatabase::ExecuteSQL() function and pass it a string that uses the CREATETABLE SQL statement.

For additional information about programmatically configuring a datasource, please see the following article(s) in the Microsoft KnowledgeBase:
110507How To Configure ODBC Data Sources on the Fly
NOTE: The information contained within this article is duplicated in the'Programming with MFC Encyclopedia' shipped with Visual C++ 4.0. Thearticle can be found by searching for "SQLConfigDataSource" and selectingthe article titled 'FAQ: Programatically Configuring an ODBC Data Source'.
More information
To create a data source, the ODBC Administrator program, which is normallyfound in Windows Control Panel as an icon labeled "ODBC", is typicallyused.

Many data sources, such as FoxPro data source, specify directories fordatabases. That is, a directory is the data source and each table iscontained in a separate file (in the case of dBASE or FoxPro, each table isa .DBF file). Other data sources, such as Microsoft Access and SQL Serverdata sources, require that something exists before a data source can beestablished. For example, when using the SQL Server ODBC driver, you wouldof course need a SQL server established. The Microsoft Access ODBC driverrequires that a .MDB file exists before it can create a data source. Whencreating an Access ODBC data source through the ODBC Administrator program,you are given two choices; you can select a .MDB file or you can create a.MDB file. There isn't a programmatic way of creating the .MDB file fromyour application. Therefore, if your application requires that you placedata into an Access data source (.MDB file), you most likely will want tohave an empty .MDB file that you can use or copy whenever you need it.

Once you have the data source established, creation of tables can easily bedone by using the CDatabase::ExecuteSQL() function and the CREATE TABLE SQLstatement. For example, if you had a database object called myDB, you coulduse the following MFC code to create a table:
   myDB.ExecuteSQL("CREATE TABLE OFFICES (OfficeID TEXT(10)"                   ", OfficeName TEXT(10))")				
The code above creates a table called "OFFICES" in the ACCESS data sourceconnected to by myDB; the table contains two fields "OfficeID" and"OfficeName".

NOTE: The field types specified in the CREATE TABLE SQL statement may varyaccording to the ODBC driver that you are using. For example, the BtrieveODBC driver will require "STRING" in place of the "TEXT" type shown in theCREATE TABLE statement above. The MSQUERY program is one way to discoverwhat field types are available for a data source. In MSQUERY, select File,choose Table_Definition, select a table from a data source, and look at thetype shown in the "Type" combo box.
References
For more information about data sources, see the "Database ClassesEncyclopedia" in the online books.
2.50 2.51 2.52 3.00 3.10
Properties

Article ID: 110508 - Last Review: 10/26/2013 02:42:00 - Revision: 4.0

  • Microsoft Foundation Class Library 4.2
  • kbnosurvey kbarchive kbdatabase kbhowto kbprogramming KB110508
Feedback