exec sp_addlinkedserver @server='FTDP',
you need to download the oledb.12.0 provider from microsoft website. just google it.
my problem is becoming even more interesting. I installed sql server enterprise edition, installed a new instance and it worked... both server authen and windows authen accessed the linked server after the ms access database was locked.
Well there were a few difference which i haven't gotten around to check whether this could be the cause of the problem:
1. VIA is disabled on the new instance and in general.
2. The new instance was configured to run on a specified user account. This is the same account i am using... its an administrator account and everything.
3. Now i have sql server enterprise edition.
So of the three, I am not sure which one solved the problem. But will update the forum when I figure this crap out.