My contribution to Forgetful MS Access Developers Everywhere

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!

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: