[AccessD] Oracle number changes to Decimal in Access 2k & 2003

I have made access tables from my queries and either changed the
DataType/Size after or while creating. I was trying to find a way
correct to set it up so someone did not have to do more than simple
procedures.  

Thanks

**************************************************
* Patricia O'Connor
* Associate Computer Programmer Analyst
* OTDA - BDMA
* (W) mailto:Patricia.O'Connor at otda.state.ny.us
* (w) mailto:aa1160 at nysemail.state.ny.us
**************************************************
 

> 
--------------------------------------------------------
This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments.  Please notify the sender immediately by reply e-mail and delete the e-mail from your system. 


-----Original Message-----

> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gary Kjos
> Sent: Thursday, July 20, 2006 09:23 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Oracle number changes to Decimal in 
> Access 2k & 2003
> 
> Are you using a Make Table query to copy the Oracle Data out? 
> You could predefine a table in the Access Database and then 
> do an Append query instead and then the numeric fields will 
> have the format you are looking for. Or you could have a 
> query for the Oracle tables that uses a 
> ReformattedFieldName:CLng([fieldname]) in it to conver to 
> Long or CDbl to convert to Double and then use that query as 
> the source to your make table or append or the export to text.
> 
> Good luck,
> 
> GK
> 
> On 7/19/06, O'Connor, Patricia (OTDA)
> <Patricia.O'Connor at otda.state.ny.us> wrote:
> > When you look at the table in ORACLE PL/SQL the datatype is 
> number but 
> > when you look at the description in Access it says number 
> with size as 
> > DECIMAL.  The precision and scale I assume is determined by whether 
> > oracle has it with decimals.  Below is a comparison
> >
> > Name                            ORACLE  ACCESS  Access
> >                                Type            Type            size
> > Precision       scale
> > ----------------------  ------- --------        ------- ---------
> > -----
> > RECORD_ID                       NUMBER(9)       Number  DECIMAL  9
> > 0
> > AREA_CD                 VARCHAR2(2)     Text            2
> > -               -
> > CHECK_SEQ_NBR           NUMBER(8)       Number  DECIMAL  8
> > 0
> > COST_AMT                        NUMBER(8,2)     Number  DECIMAL  8
> > 2
> > ADJUSTED_RECORD_ID      NUMBER(9)       Number  DECIMAL  9
> > 0
> > RECIPIENT_FEE_AMT               NUMBER(6,2)     Number  DECIMAL  6
> > 2
> > REPORT_FROM_MONTH_NBR   DATE            Number  Date/Time        -
> > -
> > RECORD_LINE_NBR         NUMBER(2)       Number  DECIMAL  2
> > 0
> >
> > I link to ORACLE 9 and 10G tables using ORACLE ODBC.
> > Errors occur from this difference. Example export to txt 
> file. Since 
> > the data is linked with read only permission, I have to 
> copy data to 
> > an access table then change the access size to long integer 
> or double 
> > then I can export the data.
> >
> > Also in the registry I have 3 HOMEx\nls_lang items.   
> Should these all
> > be the same? If so, which one is correct?
> > HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME1\NLS_LANG is set to 
> > WE8MSWIN1252 HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0\NLS_LANG is to
> > "AMERICAN_AMERICA.WE8ISO8859P1
> > HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME3\NLS_LANG to
> > "AMERICAN_AMERICA.WE8ISO8859P1
> >
> > Would this cause some of the problem above? Is there 
> anything we are 
> > supposed to do to fix this change?
> >
> > Thank you
> > **************************************************
> > * Patricia O'Connor
> > * Associate Computer Programmer Analyst
> > * OTDA - BDMA
> > * (W) mailto:Patricia.O'Connor at otda.state.ny.us
> > * (w) mailto:aa1160 at nysemail.state.ny.us
> > **************************************************
> > --------------------------------------------------------
> > This e-mail, including any attachments, may be 
> confidential, privileged or otherwise legally protected. It 
> is intended only for the addressee. If you received this 
> e-mail in error or from someone who was not authorized to 
> send it to you, do not disseminate, copy or otherwise use 
> this e-mail or its attachments.  Please notify the sender 
> immediately by reply e-mail and delete the e-mail from your system.
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> 
> 
> --
> Gary Kjos
> garykjos at gmail.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>