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
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
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