Microsoft Access and SQL Server

[KittyS]

My company is converting from Oracle to SQL Server 2005. My front end is
Microsoft Access 2003.

In the conversion, they changed our customer and transaction numbers from
decimal fields to BigInt. When I do a make table query in Access, those
fields change to text as it is my understanding Access does not support
BigInt.

Am I correct?

Many of my applications use a combination of make table and append queries
to get the data I need (most of my stuff my run standalone with no network
connection). Linking the tables through queries, reports, etc., will not
work with inconsistent data types for key fields like customer numbers.

Am I correct that I'm going to have to use all append queries so I can keep
the fields numbers like they belong??

Thanks for your help!

 

[Jeff Boyce]

Are you saying that the Access Long Int is not big enough? What kind of
values are in the customer and transaction number fields? How is it that
you are converting from "decimal" data types (?with decimal places?) to an
integer-related (?BigInt) data type?

Until you get the data type(s) nailed down, it's probably premature to be
modifying your queries. Data first, then queries, forms and reports.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KittyS]

Our database people changed the data type from decimal in the Oracle database
to BigInt in the SQL Server database.

When I do a query against SQL Server using Access - a select or make table
query - Access treats the customer number as text because it doesn't know
what to do with a BigInt data type. The data type for the customer number in
a table made from a make table query is text. If I do a select query and
sort on the customer number, it sorts like alpha characters.

If I append to a table already defined, the number fields are fine as they
retain the formatting I assigned in my table definition.

 

[Jamie Collins]

Our database people changed the data type fromdecimalin the Oracle database
to BigInt in the SQL Server database.

When I do a query against SQL Server using Access - a select or make table
query - Access treats the customer number as text because it doesn't know
what to do with a BigInt data type. The data type for the customer number in
a table made from a make table query is text.

I agree you would want to map BIGINT values to DECIMAL in Access/Jet
because that's how they are handled natively e.g.

SELECT TYPENAME(123456789012)

returns 'Decimal'.

Sadly the CDEC() function (cast to DECIMAL) is broken in Access/Jet
SQL. One workaround is to leverage the behaviour in Jet where an
arithmetic operation involving a value of type DECIMAL will coerce the
result to DECIMAL e.g. this works for me with a BIGINT column in SQL
Server and the resulting column is of type DECIMAL(28,1):

SELECT my_col + 0.1 - 0.1 AS col1
INTO NewlyMadeTable
FROM [ODBC;Driver={SQL
Server};SERVER=MyServer;DATABASE=MyDB;UID=MyUsername;Pwd=MyPassword;].MyTable

Jamie.

--

 

[Jamie Collins]

My company is converting from Oracle to SQL Server 2005. My front end is
Microsoft Access 2003.

In the conversion, they changed our customer and transaction numbers from
decimal fields to BigInt. When I do a make table query in Access, those
fields change to text as it is my understanding Access does not support
BigInt.

Am I correct?

Yes: when I try this with a BIGINT column in SQL Server I end up with
an NVARCHAR(255) column in Access/Jet. You can always change it after
the event, though e.g.

ALTER TABLE NewlyMadeTable ALTER
COLUMN col1 DECIMAL(15, 0) DEFAULT 0 NOT NULL;

....or ask the SQL Server guy to change to DECIMAL(15, 0) or whatever
suits you <g>.

Jamie.

--