Problems with Precision When Linking Tables

[seanj]

I am using MS Access 2000 to link to a table in another database. When I
look at the table's properties in Design View I show the following for the
field with the Number data type:

Field Size: Decimal
Precision: 10
Scale: 2
Decimal Places: Auto
Required: No
Indexed: No

The table I am linking to has a precision of 28 for the same field,
therefore when I try to run a query I receive the error, "The decimal field's
precision is too small to accept the numeric you attempted to add" because
one of the entries has a precision greater than 10. (I already installed the
patch that is out there for this error). Is there anyway to change the
precision in Access so that even though this is a linked table, I can change
the precision on my end without affecting the actual data in the linked table?

 

[Allen Browne]

Sean, I am unable to reproduce this problem.

I created a table in one mdb with a Decimal field, precision 28, scale 2.
Then in a different mdb, linked the table.
Opening the linked table in design view shows precision 28, scale 2.

Try deleting the linked table.
Then make sure your Name AutoCorrect options are unchecked under:
Tools | Options | General
Then compact the database.
Then link the table again:
File | Get External | Link

If it is still wrong, locate msjet40.dll (typically in \windows\system32).
Right-click it and choose Properties.
On the Version tab, what version number do you see?
It should be:
4.0.8xxx.0
The numbers at xxx do not matter, but if you do not see the 8, download SP8
for JET 4 from:
http://support.microsoft.com/kb/239114

 

[seanj]

Thanks for your help, but I tried those steps and I still have the same
problem.

What I probably should ask is if there is a way to link to an ODBC data
source using MS Access 2000 but be able to change the default precision and
scale of fields before performing the link operation?

 

[Allen Browne]

Okay; this is not a JET source. That explains why I was not able to repro
the problem.

I am not aware of a way to modify the precision/scale of a linked table.

 

[seanj]

I can't figure it out either. I am just going to use something else to run
the query.