Creating an External Table

You can create a table in an external data format through Microsoft Access by exporting the structure and data from a Microsoft Jet object to the external data format.

You create a table in an external format by using the TransferDatabase method of the DoCmd object.

Important This example uses the TransferDatabase method, which is only available in Microsoft Access; you cannot create a database or database table in an external data format by using VBA.

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 demonstrates how to create a structure in a Microsoft Access table and use that structure as the basis for a FoxPro table:

Sub CreateExternalFoxProTable()
	Dim dbs As Database
	Dim tdf As TableDef
	Dim fldContact As Field, fldPhone As Field
	Dim rst As Recordset
	Const conObjectExists = 3012

	On Error GoTo Err_CreateExternalFoxProTable
	' Create a Database object pointing to the current database. This
	' code should be run from within a Microsoft Access database.
	Set dbs = CurrentDb
	' Create a table definition for the Microsoft Access table that will
	' provide the structure information for the FoxPro table.
	Set tdf = dbs.CreateTableDef("AccessTable")
	With tdf
		' Create two text fields in the Microsoft Access table.
		Set fldContact = .CreateField("Contact", dbText)
		fldContact.Size = 30
		Set fldPhone = .CreateField("Phone", dbText)
		fldPhone.Size = 25
		' Append the newly created fields to the Microsoft Access table.
		.Fields.Append fldContact
		.Fields.Append fldPhone
	End With
	' Append the TableDef object to the TableDefs collection.
	dbs.TableDefs.Append tdf

	' 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 directory.
	DoCmd.TransferDatabase acExport, "FoxPro 3.0", _
		"C:\JetBook\Samples\FoxTables", acTable, "AccessTable", "FoxTable"
	' Delete the TableDef object for the Microsoft Access table.
	dbs.TableDefs.Delete "AccessTable"

	' Link the new table and test it by inserting a few records.
	Set tdf = dbs.CreateTableDef("LinkedFoxTable")
	With tdf
		.Connect = "FoxPro 3.0;DATABASE=C:\JetBook\Samples\FoxTables;"
		.SourceTableName = "FoxTable"
	End With
	dbs.TableDefs.Append tdf
	Set rst = dbs.OpenRecordset(tdf.Name)
	With rst
		.AddNew
		!Contact = "Nancy Davolio"
		!Phone = "555-9857"
		.Update
		
		.AddNew
		!Contact = "Andrew Fuller"
		!Phone = "555-9482"
		.Update
		
		.AddNew
		!Contact = "Janet Leverling"
		!Phone = "555-3412"
		.Update
		
		.AddNew
		!Contact = "Margaret Peacock"
		!Phone = "555-8122"
		.Update
		.Close
	End With

Exit_CreateExternalFoxProTable:
	On Error Resume Next
	dbs.Close
	Set dbs = Nothing
	Exit Sub
	
Err_CreateExternalFoxProTable:
	If Err = conObjectExists Then
		dbs.TableDefs.Delete tdf.Name
		Resume
	Else
		MsgBox "Error: " & Err.Number & " - " & Err.Description
		Resume Exit_CreateExternalFoxProTable
	End If
End Sub