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