Problems With Precision while Linking Tables

[Roby John]

I am using MS Access 2003 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: 17
Scale: 9
Decimal Places: Auto
Required: No
Indexed: No

The table I am linking to has a precision of 20 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?

 

[a a r o n . k e m p f]

I use different numeric / real / float / decimal datatypes in SQL
Server all the time without problems

perhaps linked tables aren't meeting your needs.. I'd look at moving
to SQL 2000 (because you have Access 2003) and Access Data Projects

Thanks

-Aaron

 

[Tom van Stiphout]

On Wed, 5 May 2010 03:16:01 -0700, Roby John <Roby
(e-mail address removed)> wrote:

What is this "other database"? What version of Access are you using?

When linking to an external datasource, be it Excel or MySql or any
other, Access reads the first couple of rows of data (I believe 8
lines in Excel) to guess the data type. If you have lower-precision
numbers in the first few rows it will use that data type, and the
values of subsequent rows may not fit.
You may want to try entering a maximum-precision number in the first
(by PK) row.

Oh, and ignore our resident troll Aaron, who can only sing one tune.

-Tom.
Microsoft Access MVP

 

[david]

When linking to a "text" or "excel" database, that is, one
which uses the JET "text" engine or JET "excel" engine,
Access reads the first couple of rows of data.
By default, 25 lines and 8 lines respectively. The number
of lines is set in your registry.

When linking to an ODBC database, Access use the JET
ODBC engine, which queries the ODBC database for the
data types of the fields.


Yes, you could change the precision, link, then change the
precision back.

I will be amazed if you can get that to work.

(david)

 

[Tom van Stiphout]

David is correct: simple data sources like Excel don't have a data
dictionary so Access does the next best thing which is scanning some
data (not all data because that would take too long). Real databases
like MySql have a data dictionary and Access (better: ODBC) can query
it to find out exact data types.

-Tom.
Microsoft Access MVP