Rx_
Nothing In Moderation
- Local time
- Today, 09:30
- Joined
- Oct 22, 2009
- Messages
- 2,803
A view with no unique data (part of a cube) didn't have unique columns to select with the Linked Table Manager.
So, we used the Row_Number() function to create unique values in the view.
Using Linked Table, and selecting the Entity_name column with the unique number returns #Deleted# in all rows and all columns.
If the ProductID in the inserted table has been modified, the joins won't work. One way to work around this limitation in SQL Server 2005 is to take advantage of the new ROW_NUMBER() function to create a row identifier in the view that won't be affected by changes to the ProductID. The following shows how you could alter the view to add a new durable RowID.
Answer: Started to read this and got to thinking.
The DBA set the Row_Number() return type as a bigInt. In Access linked tables, this comes back as text.
So, I made new view on the existing view using SSMS create View and added
In place of the RowNumber field, the following was substituted for a row in the SSMS View Designer:
(SELECT CAST(dbo.VW_EssBaseUploadStr.RowNumber AS integer) AS MyUnique) and made an Alias of MyUnique
Ran this, it looked the same from a linked table view.
But... it worked!
In create view SQL Server Management Studio designer, the open parens SELECT CAST...
This will create the subquery for the data type conversion. It also works efficiently in the execution plan for indexing.
The SQL window returned:
The DBA is experienced in SQL and Access Linked Tables. He is sure that the BigInt was not a problem in SQL Server 2008 r2 for Access 2007. I don't have time to verify that.
This is Microsoft SQL Server 2014 (version 12.0.2.8) using MS Office 2016.
After searching the internet, this was mentioned in a MS table showing data types in SQL Server and how they are consumed in MSAccess.
My guess is that a BigInt is so big, that Access ODBC consumes it as text that can include Null. This disqualifies a BigInt as an ODBC index.
Sorry to post a question then answer it. It is an 11 hour workday, and perhaps someone won't waste 3 hours on this as I did!
Happy 2017!
So, we used the Row_Number() function to create unique values in the view.
Using Linked Table, and selecting the Entity_name column with the unique number returns #Deleted# in all rows and all columns.
If the ProductID in the inserted table has been modified, the joins won't work. One way to work around this limitation in SQL Server 2005 is to take advantage of the new ROW_NUMBER() function to create a row identifier in the view that won't be affected by changes to the ProductID. The following shows how you could alter the view to add a new durable RowID.
Answer: Started to read this and got to thinking.
The DBA set the Row_Number() return type as a bigInt. In Access linked tables, this comes back as text.
So, I made new view on the existing view using SSMS create View and added
In place of the RowNumber field, the following was substituted for a row in the SSMS View Designer:
(SELECT CAST(dbo.VW_EssBaseUploadStr.RowNumber AS integer) AS MyUnique) and made an Alias of MyUnique
Ran this, it looked the same from a linked table view.
But... it worked!
In create view SQL Server Management Studio designer, the open parens SELECT CAST...
This will create the subquery for the data type conversion. It also works efficiently in the execution plan for indexing.
The SQL window returned:
Code:
SELECT (SELECT CAST(dbo.VW_EssBaseUploadStr.RowNumber AS integer) AS MyUnique) AS MyUnique, entity_name, clarity_id, LOB, expenditure_type, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC
FROM dbo.VW_EBUploadStrThe DBA is experienced in SQL and Access Linked Tables. He is sure that the BigInt was not a problem in SQL Server 2008 r2 for Access 2007. I don't have time to verify that.
This is Microsoft SQL Server 2014 (version 12.0.2.8) using MS Office 2016.
After searching the internet, this was mentioned in a MS table showing data types in SQL Server and how they are consumed in MSAccess.
My guess is that a BigInt is so big, that Access ODBC consumes it as text that can include Null. This disqualifies a BigInt as an ODBC index.
Sorry to post a question then answer it. It is an 11 hour workday, and perhaps someone won't waste 3 hours on this as I did!
Happy 2017!
Last edited:
