1

I'm not very familiar with MS Access, but a user would like us to create a link to a SQL Server database for him to connect to via MS Access for reporting needs. I tried on my machine, using MS Access 2013, to connect to the database. I tried both options i'm provided with when connecting to an external datasource (ODBC Database). Import the source data as well as link the data source via a linked table.

Both options import the table I want, via a Readonly account our DBA setup, but when I look at the table I noticed that the Primary Key (column name = Id) is missing when viewing in MS Access. Also a bit field we have, which is populated in the SQL Server DB with 0 or 1, in MS Access has rows with -1 instead.

I tried to connect to the database using Sql Server Management Studio, using the Read only account and i'm able to see the primary key as well as the bit field fine.

Any ideas what I might be doing wrong, to be able to see the Primary Key and the bit field properly in MS Access.

Paritosh
  • 4,243
  • 7
  • 47
  • 80

2 Answers2

1

The second question you asked about why TRUE is represented as -1 has been asked before. It's the same convention as used in BASIC. TRUE is equal to NOT FALSE. If FALSE is represented with a zero in 16 bits, and if NOT is the bitwise complement, then TRUE is represented by 16 bits set to one.

16 bits set to one, in twos complement notation, is equal to minus one.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
1

What is the datatype of the ID column if it is an Int (Integer) then it should work fine but if not it may be incompatible with Microsoft Access.

The bit column can be set to use a Check Box if you go into the table design Select field and click Lookup Tab in the bottom pane. This setting will not persist if you relink the table however.

Mark3308
  • 1,298
  • 11
  • 22
  • It is actually using a User Defined Type, which is mapped to an int. Maybe that is throwing it off. – Paritosh Nov 17 '14 at 20:42
  • I was able to locate the fix for the User Defined Type issue: http://stackoverflow.com/questions/6870320/the-execute-permission-is-denied-on-the-user-defined-table-types and https://fishandgame.idaho.gov/ifwis/portal/blog/sql-server-user-defined-data-types-not-visible-ms-access-linked-table-refresh – Paritosh Nov 18 '14 at 14:31