Export Access Table to Excel and DBF (1 Viewer)

[crimmelcp]

This command works well for exporting an access table to excel.
I cannot find the correct syntax to export an access table to a dbf file.

Thanks
Charllie Crimmel

Access to Xcel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "exporthist", "c:\exporthist.xls"

Access to DBF
DoCmd.TransferDatabase acExport, acDatabaseTypeDBF, "exporthist", "c:\exporthist.dbf"

 

[Moniker]

One, you are using TransferDatabase, and not TransferSpreadsheet. You cannot convert an Access database (mdb) into a dBASE (dbf) file. Two, even if you used TransferSpreadsheet, you cannot export into a native DBF format. You would have to save as a CSV first and import into DBF using DBASE.

 

[crimmelcp]

Export Access to DBF

According to the help files, there is a transfer database command

see attached jpg

TransferDatabase Action
You can use the TransferDatabase action to import or export data between the current Microsoft Access database (.mdb) or Microsoft Access project (.adp) and another database. For Access databases you can also link a table to the current Access database from another database. With a linked table, you have access to the table's data while the table itself remains in the other database.

Settings
The TransferDatabase action has the following arguments.

Action argument Description
Transfer Type The type of transfer you want to make. Select Import, Export, or Link in the Transfer Type box in the Action Arguments section of the Macro window. The default is Import.
Note The Link transfer type is not supported for Access projects (.adp).

Database Type The type of database to import from, export to, or link to. You can select Microsoft Access or one of a number of other database types in the Database Type box. The default is Microsoft Access.
Database Name The name of the database to import from, export to, or link to. Include the full path. This is a required argument.
For types of databases that use separate files for each table, such as FoxPro, Paradox, and dBASE, enter the directory containing the file. Enter the file name in the Source argument (to import or link) or the Destination argument (to export).

For ODBC databases, type the full Open Database Connectivity (ODBC) connection string. To see an example of a connection string, link an external table to Access by pointing to Get External Data on the File menu and clicking Link Tables. Open the table in Design view and view the table properties. The text in the Description property setting is the connection string for this table.

 

[DJkarl]

TransferDatabase lets you transfer to/from an existing database only, if the MDB or in this case DBF file doesn't exist the transfer will fail.

 

[Moniker]

Read your own post. Of course there's a TransferDatabase command. But you cannot export into native DBF format.

Database Type The type of database to import from, export to, or link to. You can select Microsoft Access or one of a number of other database types in the Database Type box. The default is Microsoft Access.

DBF is not a type you can export into, especially for a full DB. You can export tables into a common format (like CSV), and you can import straight from DBASE (albeit with some issues), but the DBs themselves are in completely different formats. VBA is Microsoft specific, for example. Even the SQL in Access is slightly different than SQL found in DB2, SQL Server, etc. In other words, you can move the data around (the contents of the tables) without too much hassle, but the structures, forms, reports, code, macros, etc. do not transfer between databases.