Results 1 to 4 of 4
  1. #1
    activechan is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    2

    Exclamation Linked tables from Access 2010 to SQL Server 2008

    I have a table on SQL server with fields defined with the following data types:



    Column Name Data Type PK/FK
    id numeric(38,0) PK, FK
    field1 varchar(56)
    field2 numeric(38,0) PK, FK
    field3 varchar(56)
    field4 date
    field5 date

    After linking to this table from Access, when I select "Design view", all of the data types in Access show as Text

    The source data in SQL server for column ID contains only numeric entries, as enforced by the data type.

    In my Access Options, under Object Designers, for table design view my default field type is Text. I can understand VARCHAR being mapped to TEXT. However, based on the data mappings from Microsoft (http://office.microsoft.com/en-us/ac...41762.aspx#BM4), NUMERIC should map to NUMBER.

    Why is this happening? How can I correct it?

  2. #2
    tanvi is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    100
    I found an article on “How Access sees SQL Server data types”

    Accessdata types differ from SQL Server data types, Access must determine the most appropriate Access data type to use for each column of each SQL Server table or view that you import or link to. For example, a SQL Server column of the data type bit is imported or linked into Access with the data type Yes/No.Another example, a SQL Server column of the data type nvarchar(255)(or smaller) is imported or linked with the data type Text, but a column of the data type nvarchar(256)(or larger) is imported as an Access Memo field. After completing an import or linking operation, you can openthe table in Design view and confirm which data types Access assigned to its fields. You can change data types of fields in imported tables. However, you cannot change data types of fields in linked tables, except by changing them in the SQL Server database itself orin an Access project that is connected to that database.
    Ref:http://office.microsoft.com/en-us/access-help/import-or-link-access-to-sql-server-data-HA010341762.aspx

    Hope the article will help you!!

  3. #3
    activechan is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    2
    Thanks. I saw that article, too. My issue is that a Numeric data type in SQL Server should map to Number in Access. According to the article, the precision of the column in SQL server should be able to be accommodated in Access.

  4. #4
    PRMiller is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Location
    St. Paul, MN
    Posts
    16
    Access ODBC-to-Jet maps SQL numeric data types with precisions greater than 28 to text. Refer to http://support.microsoft.com/kb/214854/en-us?p=1.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 01-03-2012, 12:28 PM
  2. Access 2010 through VPN and Win Server 2008
    By feguillen in forum Misc
    Replies: 1
    Last Post: 12-01-2011, 06:20 PM
  3. access 2010 windows7 nas linked tables
    By mikeb in forum Access
    Replies: 7
    Last Post: 03-18-2011, 03:59 PM
  4. Can't connect Access 2010 to SQL Server 2008 R2
    By LAazsx in forum Import/Export Data
    Replies: 6
    Last Post: 12-10-2010, 08:44 PM
  5. .adp in access 2007 to SQl server 2008
    By NoellaG in forum Access
    Replies: 5
    Last Post: 09-07-2010, 09:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums