Linked SQL Server table.

[Kev]

In SQL Server 2000 I have created a View which is Grouping
on a text field in the source table, and Summing several
numeric fields.
This View is linked to an MS Access (Access 2000) d/b
using ODBC, and any results from Access - whether directly
from this linked View or via an Access Query - are then
exported to Excel.
If the source numeric field in SQL Server is an Integer,
Float or Real there is no problem with the end result in
Excel - it is numeric. However, if this source numeric
field is Decimal or Numeric (say Precision 10/Scale 2 as
it is to record "money"), or one of the Money types is
used, then the end result in Excel is text which will not
reformat to numeric, and cannot of course be totalled.
If - in Access - the linked View (which appears as an
Access table) is opened, for the Decimal/Numeric/Money
types these numeric fields are listed as "text". It is NOT
possible to change this Data Type to anything else (going
into the Access "Design" mode).
If the Grouping in the source SQL Server View is switched
off, the end output (after refreshing the linked View in
Access) is then correctly reporting as a number.

I am given to understand that the problem is probably an
Access one. Is there a way to make this linked
SQL "Grouped" output behave correctly in Access, or is it
a general limitation of linking such a SQL Server source
to MS Access?

 

[chris]

Have you tried creating a query in access based on the
linked view (table). Itemise the fields in the query, and
for the troublesome fields cast them to a data type that
works. eg

TestField: Csng([ViewField]) or
TestField: Cdbl([ViewField])

then try exporting the query.

 

[Kev]

Many thanks for the below - works fine.
"Csng" and "Cdbl" are not code types that I'd previously
been aware of; that's the joy of this job - learning
something new most days!

-----Original Message-----
Have you tried creating a query in access based on the
linked view (table). Itemise the fields in the query, and
for the troublesome fields cast them to a data type that
works. eg

TestField: Csng([ViewField]) or
TestField: Cdbl([ViewField])

then try exporting the query.

-----Original Message-----
In SQL Server 2000 I have created a View which is Grouping
on a text field in the source table, and Summing several
numeric fields.
This View is linked to an MS Access (Access 2000) d/b
using ODBC, and any results from Access - whether directly
from this linked View or via an Access Query - are then
exported to Excel.
If the source numeric field in SQL Server is an Integer,
Float or Real there is no problem with the end result in
Excel - it is numeric. However, if this source numeric
field is Decimal or Numeric (say Precision 10/Scale 2 as
it is to record "money"), or one of the Money types is
used, then the end result in Excel is text which will not
reformat to numeric, and cannot of course be totalled.
If - in Access - the linked View (which appears as an
Access table) is opened, for the Decimal/Numeric/Money
types these numeric fields are listed as "text". It is NOT
possible to change this Data Type to anything else (going
into the Access "Design" mode).
If the Grouping in the source SQL Server View is switched
off, the end output (after refreshing the linked View in
Access) is then correctly reporting as a number.

I am given to understand that the problem is probably an
Access one. Is there a way to make this linked
SQL "Grouped" output behave correctly in Access, or is it
a general limitation of linking such a SQL Server source
to MS Access?
.

.