Importing Files

[Taff07]

Hi,

Not sure if this is the correct area for this question but doesn`t fit anywhere so here goes.

I have just split a database to a front end Back end style using linked tables to join the two ends together. This has now fallen over on the daily importation of text files. For some reason I now get the error 'Numeric Field Overflow'. This only occurs with the linked table and if I do the exact same sequence with the actual table then it works perfectly.

Does anyone know the reason for this occuring and how to get around the problem.

Thanks

Ian

 

[scking]

The first logical cause to investigate would be to track down what caused the error. That can be done either visually or programmatically using the debugger, which most people don't use. I suspect that the datatype for one of the fields in your table are numeric but maybe integer or some other datatype that is connstrained to a certain number limit (2 to the 16 for integer). For numbers you might want to use either a Long Integer or Double. Look at the data. If it is text then it is probably comma delimited. Do any of the number look too large to be stored by the data types you have?

Do you have code driving your text import process? Even if it is code behind a form. Open in design view and set checkpoints on the code and inspect the variables as they are being read into the sytem. Set up something like the following:

On Error GoTo HandleErr

... Your code

Exit_Proc:
Exit Sub

HandleErr:
MsgBox Err.Number & vbCrLf & Err.Description
Resume Exit_Proc
Resume

If you set a breakpoint on the 'Resume Exit_Proc' line and when it traps skip to the 'Resume' line and hit F8 to take a single step, it will take your processing to the line causing the error. Then just inspect all variables.

Steve King Growth follows a healthy professional curiosity

 

[Taff07]

Thanks Steve,

The problem is with the actual import itself. The code I used stopped during the import process. I have since tried the same process manually to no avail. Even if i change the only Numeric value (which was a double) to text then the same error occurs.

I know the problem isn`t within the code itslef as the same code works perfectly if the actual table is used and not a linked table.

I am used fixed width format top input the files. Is this likely to cause the problem.

Thanks for spending some time on this.

Ian

 

[scking]

Try using some troubleshooting techniques. We know that you get an error importing a fixed width text file into a linked table. Not having any idea what may be causing it we could try to eliminate the format of the datafile as the source of the problem (yes I know it works with a local table but it's still best to follow a proven process to again eliminate possible causal features). Try importing a single line from the import file. Do you get an error. Try verifying the fixed format file to ensure it is formatted correctly. Link to the file and write you own import procedure using ADO or DAO. Use the debug window to evaluate what is happening to variables you are using. Add error handling to the procedure and trap the errors. Use resume following in the handler to allow you to change the line being processed and go to the line causing the error.


On Error GoTo HandleErr

...

Exit_Proc:
Exit Sub

HandleErr:
Resume Exit_Proc ' Set a breakpoint here
Resume ' Change processing to this line and F8
End Sub

Steve King Growth follows a healthy professional curiosity