Numeric Overflow when importing text file into a linked table

[jbkirby via AccessMonster.com]

I'm having problems when importing a text file into a linked table? Can
somebody help me please?

I don't have this kind of problem before when my table is not linked meaning
it is in the same location where my front-end resides. The problem occurred
when I split the backend and the front-end. Here is the structure of my table:


Field A text
Field B text
Field C text
Field D Double

 

[Jeanette Cunningham]

Hi,
would you post some details please.
--example of rows that give the error
--which field is the error in - a, b, c or d
--details of how you are doing the import - eg from the database window, in
code - if in code could you post the code
with this info, others will be much more likely to be able to help.

Jeanette Cunningham

 

[jbkirby via AccessMonster.com]

Here is my code for the import of text file

DoCmd.TransferText acImportFixed, "Extract Import Specification", "Extract",
strInputFileName, 0

Whenever, the code goes to this line, the error "numeric overflow" appears.
It doesn't specify in what field does the error is coming. The problem
occurred when I split my application into backend and frontend.

Hi,
would you post some details please.
--example of rows that give the error
--which field is the error in - a, b, c or d
--details of how you are doing the import - eg from the database window, in
code - if in code could you post the code
with this info, others will be much more likely to be able to help.

Jeanette Cunningham

I'm having problems when importing a text file into a linked table? Can
somebody help me please?

[quoted text clipped - 10 lines]

Field C text
Field D Double

 

[Jeanette Cunningham]

jb,
would you import this text file into a new table and look at the data type
that access sets up for each field.
Which fields does Access set up as text and which as number?
Are there are any rows where there is some text in the field that access is
importing to the number field?
The numeric overflow error can happen if Access is importing into a number
field
And there is also some text in that field in the text field in place of
numbers in only one row, access gets confused and gives this error.

Jeanette Cunningham

Here is my code for the import of text file

DoCmd.TransferText acImportFixed, "Extract Import Specification",
"Extract",
strInputFileName, 0

Whenever, the code goes to this line, the error "numeric overflow"
appears.
It doesn't specify in what field does the error is coming. The problem
occurred when I split my application into backend and frontend.

Hi,
would you post some details please.
--example of rows that give the error
--which field is the error in - a, b, c or d
--details of how you are doing the import - eg from the database window,
in
code - if in code could you post the code
with this info, others will be much more likely to be able to help.

Jeanette Cunningham

I'm having problems when importing a text file into a linked table? Can
somebody help me please?

[quoted text clipped - 10 lines]

Field C text
Field D Double

 

[jbkirby via AccessMonster.com]

Which fields does Access set up as text and which as number?

Field D is numeric while the rest are text

Are there are any rows where there is some text in the field that access is
importing to the number field? Yes

The numeric overflow error can happen if Access is importing into a number
field
And there is also some text in that field in the text field in place of
numbers in only one row, access gets confused and gives this error.

If this is the case, how do I get rid of this problem?

Jeanette Cunningham

Here is my code for the import of text file

[quoted text clipped - 23 lines]

 

[Jeanette Cunningham]

One possibility is to set the number field to text in your table which
receives the import and add a separate new field for the number which is
filled after the import is completed. You may then be able to append the
numbers to the new number field in your table. Is this import done fairly
regularly and not just a once off?
It may be possible to use a query that tests for IsNumeric on the numbers
imported into the text field and only append the values that are numbers to
the new number field.

Jeanette Cunningham

Which fields does Access set up as text and which as number?

Field D is numeric while the rest are text

Are there are any rows where there is some text in the field that access
is
importing to the number field? Yes

The numeric overflow error can happen if Access is importing into a number
field
And there is also some text in that field in the text field in place of
numbers in only one row, access gets confused and gives this error.

If this is the case, how do I get rid of this problem?

Jeanette Cunningham

Here is my code for the import of text file

[quoted text clipped - 23 lines]

Field C text
Field D Double

 

[jbkirby via AccessMonster.com]

I will certainly try this one. I'm in the process of consolidating all the
answers to the problems that I have upon splitting my application into front-
end and back-end. Thank you for time Maam. Good day!

One possibility is to set the number field to text in your table which
receives the import and add a separate new field for the number which is
filled after the import is completed. You may then be able to append the
numbers to the new number field in your table. Is this import done fairly
regularly and not just a once off?
It may be possible to use a query that tests for IsNumeric on the numbers
imported into the text field and only append the values that are numbers to
the new number field.

Jeanette Cunningham
[quoted text clipped - 19 lines]