MSAccess via MSSQL Server from remote computer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

MSAccess via MSSQL Server from remote computer

Author

Topic

mrmarman
Starting Member

1 Post

Posted - 2010-02-21 : 10:56:46

Computer (A) MSSQL server and database location ( IP:192.168.2.1 - PORT:2301 )
Computer (B) MSAccess database location ( \\arman-a200 )

SELECT *
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source=\\ARMAN-A200\Network Paylasim Klasörü\Veritabani.MDB;')...Table1

This query works on Computer (A) but the same query not works on Computer (B). It says

in English :

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" 
returned message The Microsoft Jet database engine cannot open
the file '\\ARMAN-A200\Network Paylasim Klasörü\Veritabani.MDB'. 
It is already opened exclusively by another user,
or you need permission to view its data.

Please help. If I place the MSAccess MDB file to the Computer (A)'s any local folder then it works both computer with no problem. I need separated computers beacuse of two different / separated project.

SETUP FOR BOTH Computers
Computer (A)
* MSSQL Server 2008 Express installed.
* MSSQL TCP connection enabled. Server : 192.168.2.1,2301\SQLSERVER
* MSSQL Remote connection enabled.
* MSSQL AdHoc (for LinkedServer) enabled.
* Temp folder for active logon user shared with everyone group by read/write

Computer (B)
* Nothing installed...
* The folder shared which contains MSAccess mdb database. ( veritabani.mdb )
* Shared Folder for everyone group for read/write
* Shared Folder Network Name : \\ARMAN-A200\Network Paylasim Klasörü\

Both (A) and (B)
* Windows 7 - UAC - Disabled
* Windows 7 - Password Protected Share - Disabled
* Windows 7 - Firewall - Disabled
* Virus Software - Firewall - Disabled
* Local INTRANET - file://arman-a200 inserted to area list for security reason (trusted)

The query alternatives that I tried below. Success on Computer (A) but not success on computer (B)

Query 1.

SELECT *
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source=\\ARMAN-A200\Network Paylasim Klasörü\Veritabani.MDB;')...Table1

Query 2.

SELECT *
FROM OpenRowSet    ('Microsoft.Jet.OLEDB.4.0',
   '\\ARMAN-A200\Network Paylasim Klasörü\Veritabani.MDB';'admin';'', Table1)

I created a ARMAN_LINKED_SERVER named LinkedServer for MSAccess database and tried below, it works on (A) but not works (B) either.
Query 3.

SELECT * FROM ARMAN_LINKED_SERVER...Table1

This query works on Computer (A) but the same query not works on Computer (B). It says

Please help. If I place the MSAccess MDB file to the Computer (A)'s any local folder then it works both computer with no problem. I need separated computers beacuse of two different / separated project.

Subscribe to SQLTeam.com

Resources