Link SQL Server data to MS Access database with filters

Question

I would like to create a link from an SQL Server database to an MS Access database for reporting purposes. I don't need all the data from the table just the past few years worth. Is there a way through Access VBA or MS Access tools to only bring in the rows I need rather then all rows?

Answer 1

I talked to our DBA. I'm going to create a view. cloudsafe thanks for the suggestion, I just didn't have the time to investigate QueryDef in Access, but I've I made a note of it for possibly future usage.

Answer 2

Connect to the SQL Server database in Access using the External Data tab via ODBC and use SQL in ACCESS or the query desinger on the Create Tab to query the data you need. SELECT * from TABLE WHERE DATEFIELD > whatever date

Answer 3

This is an example of how a parameter table works in SQL Server, that can easily be updated from Access. The table is updated or inserted with the current username and the filter value, changing the rows returned by the view.

create table Params ( usrname as nvarchar(100) CONSTRAINT [df_load_date] DEFAULT @@SUSER_NAME , pvalue as nvarchar(100)) Insert into params (pvalue) values ( 'MytableName') Create view vw_test as select * from sysobjects where name in ( Select pvalue from Params where usrname = @@SUSER_NAME)