Microsoft Access uses the Microsoft Jet database engine, which is where the "intersting" SQL syntax originates. I don't know of any way to get Microsoft SQL to use the Jet dialect directly (from within MS-SQL), but you can get there another way.
You can leave the data in Microsoft SQL, but create an ADP file so that you get a Jet "front end" that can use the Jet syntax to access data from a Microsoft SQL Server.
I'm trying to remember to visit here more often, but I'm not always good about remembering especially when under deadlines (like I am right now). If you have questions about this process, you can post them here or you can visit DBForums which is another iNet site that deals specifically with database issues. You can visit the
DBForums Microsoft Access Forum or
DBForums Microsoft SQL Forum for more information.
-PatP