Linked Foxpro table error : Decimal field precision too small

Hi Experts,

I'm using an Access 2000 database with Access 2003 SP1.  I linked some Foxpro tables via ODBC.  Most tables work just fine, but there's one that gives me the error "The decimal field's precision is too small to accept the numeric you attempted to add."

This error occurs on just a few records.  I'm able to open the table and see my data except for those few records, they show as "#Error"

I need these tables to be linked and can't change their design as they are used be another application wich can't be updated...

Any ideas ?


LVL 15
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Assuming that you are entering data into a form which is bound to the linked table.

On the appropriate controls set an event to format the value to the acceptable precision.

This could be a matter of checking the foxpro field for it's datatype and then looking up the acceptable values for that datatype then using either format or one of the Type Conversion Functions (check access help for them). For example

s = Format(13.9999999, "0.0000")

s is now a string with value 14.0000

I'm guessing a string ought to implicitly convert to a number. If not you can use the apprpriate type conversion and then put that into the field.
jmantha709Author Commented:
This solution doesn't work because the error arrives before the format function

s = Format(MyField, "0.0")

As soon as I try to retrieve the value of MyField I get the error, so I can't use it in any way.
Wait so you are getting the problem with the data coming in to the database?

Check out the design properties of your linked table. You need to kow what datatype the column giving you grief is. Then find the datatype in foxpro. Find an agreeable one in access and change to that.

Otherwise I guess you are looking at using passthrough queries maybe? I don't know if you can even do that with fox pro, I assume not. Can you not import the data and use a local table or do something which gives you control over that columns values?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.