How to trap error 3047

[Nicodemus]

Hello,

while transferring text files to tables through VBA, I sometimes get the
'Record is too large' error. These text files have a standardised layout, but
it happens that some of them got corrupted before I upload them into my DB.
To prevent the VBA code to break, I use the 'On error goto err_mgr' solution:
On Error GoTo ErrMgr
....
DoCmd.TransferText acImportDelim, "myImportSpecs", "myTable", "myFile", False
....
ErrMgr:
Select Case Err
Case 3011
...
Case 3047
'unexpected file layout
...
End Select

I assumed that I could trap this 3047 error code and skip the corrupted
files, but an error message box pops up BEFORE it is detected by the 'On
error' function.

Is there another way to handle this error with VBA ?
Thx in advance for any help.
Nico

 

[Allen Browne]

Access sets aside a 4k page to hold records. If you have more than about
4000 characters in a record, it won't cope. That's what the 'record is to
large' is telling you. If your text file is corrupted so the end of line
characters are not found, that could trigger the error.

(It's actually a bit more complex than that, with some overhead per record
and some per field, and dependency on Unicode, with only 2k available in the
pre-unicode versions i.e. A97 and earlier.)

If you canot use TransferText, you may be able to Open the file for input,
and read it one line at a time. If the line is too long, you can discard the
data. Otherwise you can AddNew to a Recordset, parsing the line at the
delimiters with Split() and assigning the values to the fields.

 

[dymondjack]

You could always do this:

On Error GoTo ErrMgr
....
....
On Error Resume Next
DoCmd.TransferText...
If Err.Number <> 0 Then GoTo ErrMgr
On Error GoTo ErrMgr
....
....

This would keep your error 'hidden' but the results of the import may be
unstable. I *think* this error is not going to your handler due to that fact
that it's coming from inside the transfer itself, rather than the code, but
I'm not positive about that.

The above usually isn't the safest route, but it may work.

hth


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[Nicodemus]

Hi Jack,

yes, your idea might work, but as you said might lead to unstable results...
thx any way.

Hi Allen,
I have use your idea. Just loading the first line of the file, checking its
length, and if more than 255 characters, then skip the file.
We know that the length will never exceed 255.

Thank both of you for your help.
Nico

 

[Allen Browne]

Hi Allen,
I have use your idea. Just loading the first line of the file, checking
its
length, and if more than 255 characters, then skip the file.
We know that the length will never exceed 255.

Sounds like a good workaround.