Querying MS Access, then SQL Server from Command

[Phantek]

My primary data source is an SQL Server database to which I have very limited view-only rights. As a result, I have created some separate tables in an MS Access database to help me work with the data in the SQL Server.

I am able to use the Database Expert to link the two data sources together. The problem now is that I need to gather results from an MS Access table, and then use those results within a Command on the SQL Server database.

So the data I need from Access is a list of Fiscal Periods - the period_key, period_name, start_date, and end_date. I will then take those values and use them in the SQL Server Command to limit the results that I am retrieving (so only within the date ranges), and then to count the number of transactions per location per fiscal period (fields include t_id and t_date). Due to the volume of results, I want to have the counting completed on the SQL Server itself rather than asking Crystal Reports to count them.

Is there any way that I can use a Command in SQL Server to query MS Access and then use the results as part of the query on the SQL Server?

Thank you in advance for any assistance.

 

[PeterDimitrov]

You may try to link SQL Tables in Access. Then you can query them as they are part of the Access database

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.