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

Question

hi

Please solve this error ?

I want to read the mdb file and push the data to tables in Sql Server 2005

i am using the code like:

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

or

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

I am getting an error like "Msg 7303 Level 16, State 1
Cannot initialize the datasoure oblect ole db provider 'Microsoft.jet.oledb.4.0' for linked server "null"

but its working in Sql Server 2008

Tanks & Regards
Narayanan

Narayanan vd · Answer

Hi Trivedi Thanks for quick reply hope i will complete. regards Narayanan

Jignesh Trivedi · Answer

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.

Narayanan vd · Answer

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

Jignesh Trivedi · Answer

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.