Access Query from MS SQL Linked Table Query using Text Criteria

Question

I have a table in a SQL Server 2012 instance, like so

 CREATE TABLE [dbo].[Test](
 [SampleDateTime] [datetime] NULL,
 [Unit ID] [nvarchar](4) NULL,
 [WS Avg 2min] [float] NULL,
 [WD Avg 2min] [float] NULL,
 [WGS 10min] [float] NULL,
 [WGD 10min] [float] NULL,
 [Air Temp] [float] NULL,
 [Rel Humidity] [float] NULL,
 [Dew Point] [float] NULL,
 [Pyranometer] [float] NULL,
 [Quantum] [float] NULL,
 [Air Pressure] [float] NULL,
 [Snow Level] [float] NULL,
 [Rainfall] [float] NULL,
 [PW Current] [varchar](10) NULL,
 [Visibility] [float] NULL,
 [CBase 1] [float] NULL,
 [CBase 2] [float] NULL,
 [CBase 3] [float] NULL,
 [Vert Vis] [float] NULL
 ) ON [PRIMARY]

connected to MS Access (2010) via an ODBC linked table (SQL Server Native Client 11.0)

When I open the table, I see all of the data

However, when I try a simple query

SELECT dbo_Test.* FROM dbo_Test
WHERE ( (dbo_Test.[Unit ID])="BASE") ;

I am still getting all the rows, not just the rows where [Unit ID] is "BASE" The same query in SQL Server Mgt. Studio works just fine with only the expected results returned.

I also notice that when sorting the linked table by [Unit ID], it does not sort properly. There will be rows with data just not sorted like I would expect. (See image below, sorted Ascending by [Unit ID])

Is there a way to get this linked table to behave properly?

Andre · Accepted Answer · 2015-08-20 16:10:03Z

1

Your table seems to be lacking a primary key. This can cause all sorts of issues (e.g. you won't be able to write to that linked table). Although I would be surprised if the effect you see is caused by that.
But try adding an IDENTITY column as primary key, and relink the table from Access.

// Edit: too late :)

answered Aug 20, 2015 at 16:10

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Julian Race · Accepted Answer · 2015-08-20 16:09:35Z

1

It turns out the issue was that the table did not have a primary key established. Due to our data, I could not use SampleDateTime as a primary key. So, I created a unique key on SampleDateTime + Unit ID instead

ALTER TABLE [dbo].[Test] ADD CONSTRAINT [KEY_AWAData_DateandUnitID] UNIQUE NONCLUSTERED 
(
    [SampleDateTime] ASC,
    [Unit ID] ASC
) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Now MS Access is happy to execute queries and sort as expected.

answered Aug 20, 2015 at 16:09

Julian Race

711 silver badge7 bronze badges

Collectives™ on Stack Overflow

Access Query from MS SQL Linked Table Query using Text Criteria

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related