I get asked this question a lot. Figured I'd post it here so I can share my answer with everyone else. This person isn't one of my students (yet) so I couldn't tell him to post it here.

Q: I am currently using Access and linked tables which consists of about 10 CSV and Excel files. The reason I used linked tables is because the files are updated every day with the most recent data (Via scripts) from our Active directory user and computers. I did not know how to do it any other way and it seemed simple. I would like to upgrade this model to SQL so I could have more granular security for my users. If I move this model to SQL, are tables able to updated automatically just like in Access since they are just reading a file. I wanted to get you feedback and make sure I can accomplish this before I move forward. If you think this is possible, do you have recommendations on which courses or classes I would need to purchase? Thanks in advance. Sam

A: Is it POSSIBLE? Yes. You can use OPENROWSET and OPENDATASOURCE. It's a pain to get working reliably though.

A better solution would be to use an Access database as a go-between. Link a table to the SQL database, and another table to the Excel sheet. Make a query to pull from Excel and update to SQL.

That's how I would do it, especially since you only need to update once a day.