SQL Server Performance Forum – Threads Archive
Casting Datatype Using a View
Because of a misfeature in ODBC/Jet, Access cannot view SQL tables that use a bigint as a primary key. The proposed workaround is to create a view that just mirrors the structure of the table, but casts the bigint as an int. Can someone explain how to do this? Thanks.CREATE VIEW dbo.vwTable
AS SELECT CAST(idcol AS VARCHAR(100)) AS idcol, ………….
FROM dbo.MyTable GO In Access, in the dialogs for linking tables, you will notice that views are listed among the tables, so it’s exactly the same approach as with tables. If the SQL table has more than one index defined, then for Access the name of the PK must come first in alphabetical order of the index names – Access can act funny if that’s not the case (so make it a rule that the name of the PK must start with "a_" or something).
This works great. But, the PK on the ID column is not getting through to Access. Thoughts?
quote:Originally posted by Adriaan
CREATE VIEW dbo.vwTable
AS SELECT CAST(idcol AS VARCHAR(100)) AS idcol, ………….
FROM dbo.MyTable GO In Access, in the dialogs for linking tables, you will notice that views are listed among the tables, so it’s exactly the same approach as with tables. If the SQL table has more than one index defined, then for Access the name of the PK must come first in alphabetical order of the index names – Access can act funny if that’s not the case (so make it a rule that the name of the PK must start with "a_" or something).
AS SELECT CAST(idcol AS VARCHAR(100)) AS idcol, ………….
FROM dbo.MyTable GO In Access, in the dialogs for linking tables, you will notice that views are listed among the tables, so it’s exactly the same approach as with tables. If the SQL table has more than one index defined, then for Access the name of the PK must come first in alphabetical order of the index names – Access can act funny if that’s not the case (so make it a rule that the name of the PK must start with "a_" or something).
Is there a ‘natural’ PK, besides the bigint? If so, and the main interaction is through Access, then make the natural key the primary key, and make sure it comes before the bigint constraint alphabetically. One way to tell that Access doesn’t recognize the PK in a SQL Server table is when you don’t see a line for a new record on an ODBC linked table.
]]>