exec sp_addlinkedserver @server='FTDP',

@srvproduct= 'Access',

@provider= 'Microsoft.ACE.OLEDB.12.0',

@datasrc= 'C:\ftdp.mdb'

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.