Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rating: Thread Rating: 28 votes, 5.00 average. Display Modes
Old 09-24-2007, 07:47 AM   #1
crimmelcp
Registered User
 
Join Date: Sep 2007
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
crimmelcp is on a distinguished road
Export Access Table to Excel and DBF

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"

crimmelcp is offline   Reply With Quote
Old 09-24-2007, 10:11 AM   #2
Moniker
VBA Pro
 
Join Date: Dec 2006
Location: Dallas
Posts: 1,567
Thanks: 0
Thanked 10 Times in 9 Posts
Moniker will become famous soon enough Moniker will become famous soon enough
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.
__________________
~Moniker

(If you've been helped by me or anyone else, please add to their reputation by clicking the "scales" icon in the upper-right.)
Moniker is offline   Reply With Quote
Old 09-24-2007, 10:55 AM   #3
crimmelcp
Registered User
 
Join Date: Sep 2007
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
crimmelcp is on a distinguished road
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.
Attached Images
File Type: jpg access_export2.jpg (17.8 KB, 1124 views)

crimmelcp is offline   Reply With Quote
Old 09-24-2007, 11:03 AM   #4
DJkarl
Newly Registered User
 
Join Date: Mar 2007
Location: Dallas, TX
Posts: 1,028
Thanks: 0
Thanked 63 Times in 62 Posts
DJkarl will become famous soon enough DJkarl will become famous soon enough
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.
DJkarl is offline   Reply With Quote
Old 09-24-2007, 11:57 AM   #5
Moniker
VBA Pro
 
Join Date: Dec 2006
Location: Dallas
Posts: 1,567
Thanks: 0
Thanked 10 Times in 9 Posts
Moniker will become famous soon enough Moniker will become famous soon enough
Read your own post. Of course there's a TransferDatabase command. But you cannot export into native DBF format.

Quote:
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.

__________________
~Moniker

(If you've been helped by me or anyone else, please add to their reputation by clicking the "scales" icon in the upper-right.)
Moniker is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Access Limit of 148000 Records? amerifax Tables 16 10-15-2008 10:04 AM
Too many rows to export to Excel Autoeng Modules & VBA 8 09-24-2007 09:54 AM
dbf file not importing to access 2003 jnixon General 3 11-04-2005 06:42 AM
DoCmd.RunSQL statement error. MS Jet DB not able to find table rushitshah Modules & VBA 1 09-06-2005 10:25 PM
importing dbf files Icehousman2 Tables 5 11-29-2004 06:06 AM




All times are GMT -8. The time now is 12:55 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World