Error Exporting Data to a DBF file

[Joe4]

We have some processes that need to create DBF control files for a non-Micorsoft application. We used to create these files in Excel. However, we recently convrted to Office 2007, and Microsoft decided that no one really needs to create DBF files from Excel anymore, so this functionality is no longer needed (don't even get me started!).

Anyway, we have been getting around this by using Access to create the DBF files. However, we have now come across one that just won't work. Whenever I try to export the data out of Access as a DBF, I get an error message that says "Field will not fit in record".

I searched the web for this error message, and none look like they will apply. Here is what I have checked:

1. All 26 field names are alphanumeric, and the only punctuation used in any of them is the underscore.

2. All field names are 10 characters or less.

3. All fields are Text fields and are formatted with lengths between 4 and 53 (and all entries are less than the field widths)

What else could it be? A few of the fields have no entries in any record (for example, one field is named PR4, and no records have an entry in this fields). Could these null values be causing the issue?

Thanks.

 

[SydneyGeek]

Not sure Joe, but is it worth exporting to a fixed-width text file and scanning it to see if anything is out lof line?
Something that has caused me grief in the past is carriage returns in fields (I get this with imported data occasionally). Not always the return as such that causes the problem, but then users add the data again because they can't see it in the text box, and you blow the size limit on the field at export time.

With the nulls... would it be worthwhile replacing nulls with a space character to see if that helps?

Denis

 

[Joe4]

Denis,

That's not a bad idea. I will give it a go tomorrow when I am back in the office. I was just wondering if there was anything other than the conditions I listed which would cause such an error to return. I couldn't find any others on my search of the internet.

 

[Joe4]

Denis,

I tried your suggestion, and saw nothing out of the ordinary. So I decided to try exporting to a DBF, first just one field, then two, etc until I encountered the issue.

I found where it is bombing out, and it didn't matter what field I put there, it kept bombing out. It didn't make any sense to me, as the total length of all 26 fields combined is 229. Then I discovered what is going on.

I have 26 fields, all text. My Excel file is linked to Access. I am trying to create the DBF file from this linked table. I discovered that no matter if you set the column widths in Excel, in linked Excel files, Access sets the column width of each Text fields as 255 (and it does not appear one can override field widths in linked tables).

Once I figured that out, a few possible solutions presented themselves:

1. Import the file into Access and modify the field width properties;
-or-
2. Create a Make-Table query which copies the data from the linked table into a new table, and modify the field width properties on the new table. Use this table for the export.

So I opted for the latter (just because it fits in better with my automation), and it works fine.

Just one more extra step in the process because they took away the ability to create DBFs from Excel in 2007. If things ever settle down here, I am going to take a look at the VBA code you posted a while back that tries to create DBF files from Excel 2007 using VBA (http://www.mrexcel.com/forum/showthread.php?t=369994).

 

[SydneyGeek]

Glad to hear it's sorted Joe, and a useful heads-up about text field sizes if you link to Excel. Since Access lost the ability to edit linked Excel files I have aways imported into temp tables -- once you're set up, it's quick to run and subsequent performance is better anyway.

Denis