Creating an External Table

You can create a table in the format of an external data source by creating a Microsoft Access table and exporting it. For example, you can create a table in Microsoft Access and export it as a Microsoft Excel worksheet. Microsoft Access creates a worksheet in Microsoft Excel that contains a copy of the data from your Microsoft Access table. Field names from the Microsoft Access table are placed in the first row of the worksheet.

You can create an external table by using either the Microsoft Access user interface or Visual Basic code.

To create an external table by using the Microsoft Access user interface

1 Create the Microsoft Access table.

2 In the Database window, click the name of the table you want to export.

3 On the File menu, click Save As/Export.

4 In the Save As dialog box, click To An External File Or Database, and then click OK.

5 In the Save As Type box, click the type of data source you want.

6 Select the drive and folder you want to export to.

7 Double-click an existing file or enter a new name in the File Name box, and then click Export.

Caution Usually, if you export to an existing file, Microsoft Access deletes and replaces the data in that file. The exceptions occur when you export to a Microsoft Excel version 5.0, 7.0, or 8.0 workbook, where data is copied to the next available worksheet.

To create an external table by using Visual Basic code

1 Open the database you want to create the table in. If it is the current database, use the CurrentDb function to return an object variable that represents the current database. If it isn't the current database, use the OpenDatabase method to open the database you want.

2 Use the CurrentDb function to create a Database object that points to the current database.

3 Use the CreateTableDef method of the Database object to create a table definition for the Microsoft Access table.

4 Use the CreateField method of the TableDef object to create one or more fields in the Microsoft Access table.

5 Use the Append method of the Fields collection to add the new field or fields to the Microsoft Access table.

6 Use the Append method of the TableDefs collection to create the Microsoft Access table.

7 Use the TransferDatabase method to create the external table in the specified folder.

8 Use the Delete method of the TableDefs collection to delete the Microsoft Access table definition.

After you've created the table, you can access it from the table's native application or you can link or open it as you would any other external table. The following example creates a Microsoft Access table, uses it as the basis for a FoxPro table, and then links the new external FoxPro table to a Microsoft Access database.

Public Sub CreateExternalFoxProTable()
   Dim dbs As Database
   Dim tdfNewExternalDatabase As TableDef
   Dim tdfTestNewTable As TableDef
   Dim fldContactName As Field
   Dim fldPhoneNumber As Field
   Dim qdfInsertRecords As QueryDef
   Dim rstCheckRecordCount As Recordset
   Dim intNumRecords As Integer
   ' Create a Database object that points to the current database.
   Set dbs = CurrentDb

   ' Create a table definition for the Microsoft Access table that
   ' provides the structure information for the FoxPro table.
   Set tdfNewExternalDatabase = dbs.CreateTableDef("AccessTable")

   ' Create two Text fields in the Microsoft Access table.
   Set fldContactName = tdfNewExternalDatabase.CreateField("Contact_name", dbText)
   fldContactName.Size = 30
   Set fldPhoneNumber = tdfNewExternalDatabase.CreateField("Phone_number", dbText)
   fldPhoneNumber.Size = 25

   ' Append the newly created fields to the Microsoft Access table.
   tdfNewExternalDatabase.Fields.Append fldContactName
   tdfNewExternalDatabase.Fields.Append fldPhoneNumber
   ' Append the TableDef to the TableDefs collection to create the table.
   dbs.TableDefs.Append tdfNewExternalDatabase
   ' Use the TransferDatabase method to export the Microsoft Access table's structure
   ' to a FoxPro table; this creates a new FoxPro table in the specified folder.
   DoCmd.TransferDatabase acExport, "FoxPro 2.6","C:\FoxPro\Data", acTable, _
       "AccessTable","FoxTable"

   ' Delete the TableDef for the Microsoft Access table.
   dbs.TableDefs.Delete "AccessTable"
   ' Link the new table and test it by inserting some records.
   Set tdfTestNewTable = dbs.CreateTableDef("FoxTable")
   tdfTestNewTable.Connect = "FoxPro 2.6;DATABASE=C:\FoxPro\Data;"
   tdfTestNewTable.SourceTableName = "FoxTable"
   dbs.TableDefs.Append tdfTestNewTable
   Set qdfInsertRecords = dbs.CreateQueryDef("Insert Records")
   qdfInsertRecords.SQL = "INSERT INTO FoxTable VALUES ('C. J. Date', '555-5050')"
   qdfInsertRecords.Execute
   qdfInsertRecords.SQL = "INSERT INTO FoxTable VALUES ('Roger Penrose', '333-5050')"
   qdfInsertRecords.Execute
   qdfInsertRecords.SQL = "INSERT INTO FoxTable VALUES ('Alan Turing', '011-56-5050')"
   qdfInsertRecords.Execute
   qdfInsertRecords.SQL = "INSERT INTO FoxTable VALUES ('Niklaus Wirth', '330-2430')"
   qdfInsertRecords.Execute
   ' Count the records to ensure that four records were added.
   Set rstCheckRecordCount = tdfTestNewTable.OpenRecordset()
   rstCheckRecordCount.MoveLast
   intNumRecords = rstCheckRecordCount.RecordCount
   MsgBox "Successfully added " & intNumRecords & " records."
   rstCheckRecordCount.Close
   dbs.Close
End Sub

Casa de Bender