How to Read the *.mdb(microsoft access) files using sql server 2005

Hi Trivedi,

Thanks for quick reply hope i will complete.

regards
Narayanan

hi,

The linked server feature in x64 versions of sql server is NOT working against access/ace/accdb files using ANY driver.

Please go through.

http://connect.microsoft.com/SQLServer/feedback/details/587897/connecting-via-a-linked-server-to-an-access-2010-database-file

hope this help.

Hi Jignesh Trivedi,

exec sp_linkedserver 'linkedadvisor','Acess','Microsoft.jet.oledb.4.0',@mdbfilepath

select * from linkedadvisor...tblname

or

select * from opendatasource('Microsoft.jet.oledb.4.0','datasource='path')...tbl_name

Its working in 32 bit machine but not working in 64 bit machine.

Is there any way to solve this please help me

Thanks and Regards
Narayanan

Hi,

for this error there are couple reason.

1) path may wrong.
2)file is open by another user oe program.
3)OPENROWSET is a just like linked serverand create temporary DSN (Data Source Name) OLEDB provider will execute under the account that called it.it might have not Write access to the TEMP directory for the SQL Server service account.
Also you can try with sp_addLinkserver

sp_addlinkedserver 'linkedadvisor', 'Access', 'Microsoft.Jet.OLEDB.4.0',
   'c:\mydata\linkedadvisor.mdb'

SELECT * FROM linkedadvisor...tbl_name
 
hope this help.