Normally, it is easy enough to setup a Linked Server on SQL Server to other data sources. Problems are usually caused by one of the usual culprits that have to be addressed
SQL Logins simply do not work well when trying to do this type of setup
The Windows login has to have permissions to the file (on a drive or network share)
The appropriate drivers have to be setup (64 bit / 32 bit)
Read on for a few different errors and their solutions.