c# query ms access against sql server

Question

I have been asked to setup a course leaflet system for a college. For whatever reason in the past their current system is not linked to their actual course file, they wish to close this link so course leaflets are related to actual course codes. Unfortunately their course file is a ms access database linked to many of their existing systems (cannot easily be upgraded/moved). Since the course leaflets are going on the web it is a requirement with their hosting to use a sql server database.

This means I need to query between the two internally so they can work out what courses they have without a leaflet, I would not like to add ad hoc queries to the access database to do this.

What is the best way to do this in C#, I think LINQ can do it but have not learnt it yet, should I learn it for this project or is there an easier way?

I thought about a linked server to the ms access db but this would require moving the db to the sql server. Another difficult task as from what I can tell links to the database are hard coded.

Answer 1

Why do you need the Access file to the SQL server to create a Linked Server? Just put it on a network share with appropriate security and create your linked server like that.

Answer 2

To add, LINQ has nothing to do with SQL or Access or anything else, it's for querying in memory object collections. Some linq providers allow you to use that to access your DB in question, but they won't be much help in this situation, I think.

Answer 3

Just how often does the course file change? Fifty times a day? Once a month?

What about creating the appropriate tables in the SQL Server database? Then every so often (as often as necessary to stay reasonably current), clear those tables out and repopulate them from the Access database. You could set this to run every morning at 3 a.m. or whatever. Or you could just do it periodically whenever the tables change significantly.