When you’re developing a database that will work with other formats you’ll often find a need to export to the ol’ standard DBase (.dbf).
You’ll use the TransferDatabase method, either as a macro command, or in VBA it’ll look like:
DoCmd.TransferDatabase(acExport,”dBase 5.0″,”C:\Temp\”, ,”tblTransferTemp”,”newdb.dbf”)
This works fine in Microsoft Access 2003, Microsoft Access, 2007, and Microsoft Access 2010. You can also export to DBase III, DBase IV, or DBase 5.0 in addition to many other formats.
So you run the macro or VBA code, the file is created, then you notice that there’s a problem. Entire fields are blank. Entries aren’t there. Data missing. If you dig deeper you may notice that the Access TransferDatabase method has not saved ANY of your text or string data into the DBF file.
What’s going on? Access strings are 255 characters by default. The maximum length of a DBase string/text field is 254 characters. So the Access TransferDatabase command sees a 255-character field and rather than truncating it, simply does not export that field.
How do you fix it? Easy!
Truncate your string fields before trying to export them. In the simple example I was working on the other day I cut off the text at the 100th character in the underlying make-table query…problem solved.
So if your dbase export files are missing data , first check the tables that they’re derived from and make sure the data looks good, second check the string lengths and data types!
