Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Datatype - SQL Server to Access (Access 97)

    Hi,

    I've created "linked tables" from SQL Server to Access. The datatypes for 'decimal' and 'numeric' in SQL Server will changed to 'text' in Access. Why is it so? Any way to correct it? Thanks...

    Regards,
    88

  2. #2
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    USA
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Datatype - SQL Server to Access (Access 97)

    I believe that it has to do with the Size, Precicision, and Scale of the data fields.

    In one of my Access 97 apps SQL numeric fields with 5(5,0) are shown as Number, Long Integers in Access.
    Numeric fields with 13(22,6) and 13(20,0) are shown as Text in Access.

    I can think of three ways to solve the problem:
    (1) Modify the SQL tables to a lesser scale and precision
    (2) Use the conversion functions (CDbl, CInt, CLng, etc.) to change the data type in queries, reports, etc.
    (3) Updgrade to Access 2000, which doesn't seem to have this problem.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Datatype - SQL Server to Access (Access 97)

    The only data type in Access that is even similar to the decimal or numeric types in SQL Server is the currency field. Those data types in SQL Server are essentially BCD (Binary Coded Decimal) encoded types which eliminate the round-off problems you get with floating point numbers like you have in Access. You really don't want to use them in a database where Access is used as the front-end, or at least certainly not where you need to do calculations.
    Wendell

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Datatype - SQL Server to Access (Access 97)

    Which is precisely why you create views and use those instead of trying to work directly with SQL tables. There are a number of field types in SQL Server that have to be converted to something else so Access can work with them.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Datatype - SQL Server to Access (Access 97)

    Hi Charlotte,

    Do you have detailed instructions or code that attaches to SQL Server Views from Access 97?

    Thanks

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Datatype - SQL Server to Access (Access 97)

    Views are attached to in the same fashion that you refer to tables - whether using DAO or ADO. They can be linked using ODBC, and appear to Access as a SQL Server table. However there is an issue of updatability as it pertains to indexes - in older versions of SQL Server you couldn't have an index on a view. SQL Server 2000 does support that, though not all SQL types will be familiar with it. If the view doesn't have an index, Access will ask you to identify the primary key of the table. If you don't, it won't let you do updating with any query that uses the view, and in later versions, may not even let you update the table.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •