MSAccess via MSSQL Server from remote computer
-
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 Paylaşım Klasörü\Veritabani.MDB;')...Table1
[h2]This query works on Computer (A) but the same query not works on Computer (B). It says[/h2]
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 Paylaşım Klasörü\Veritabani.MDB'.
It is already opened exclusively by another user,
or you need permission to view its data.
[h2]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.[/h2]
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 Paylaşım 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 Paylaşım Klasörü\Veritabani.MDB;')...Table1
Query 2.
SELECT *FROM OpenRowSet ('Microsoft.Jet.OLEDB.4.0',
'\\ARMAN-A200\Network Paylaşım 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...Table1I'm looking forward your posts.
Thank you for your comments.
-
Not a clue. Have you considered posting this question in the MS Access forum too?
-
It the security model of the Linked Server or of the configuration settings.
If you create an Access Database on your local machine are you able to Link to a Access table on Server B?
I doubt that the Database is opened in Exclusive mode but to the server & check for an ldb file or Computer Management & verify that the file is not opened just to be sure.
Why isn't the Linked Server enabled on Server B?
Why isn't remote connections allowed on Server B?
Why isn't TCP/IP enabled on Server B?
http://www.eggheadcafe.com/software/aspnet/32120578/linked-server-connection.aspx
http://www.sqlservercentral.com/Forums/Topic185045-5-1.aspx
BTW, thanks for translating the error message to English. For a minute I thought I was going to have to do it myself. 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
@paul-2 White
I considered but it suppose that a MSSQL Server problem.
@Welsh Corgi
thank you for comment.
* I have only one SQL Server installed on Computer (A) and other computer (B) has not installed any server. Only one dummy / slave one of WiFi connected notebook or will be a netbook.
* Yes, Computer (B) can locally access it's own harddisk and/or any other computer via network.
* Computer (A) also can locally access it's own harddisk and/or any other computer via network.
this problem only on MSSQL Server by remote connection. Locally it works with no problem.
I am able to link any MSAccess MDB file successfully both computers (A) and (B) by standart ADO connection. There is no problem with standart Jet 4.0. Windows's default ADO scheme allowed to connect for it on any local or networked too.
Why isn't the Linked Server enabled on Server B?
Why isn't remote connections allowed on Server B?
Why isn't TCP/IP enabled on Server B?
Computer (B) has not any MSSQL server so that no linked server configuration. It's only Computer (A). I enabled this for to use OpenDataset command.
- Remote connection for standart MSSQL database allowed, no problem. The problem only JET 4.0 via MSSQL
Basicly if you want me to draw a schema, i can say like below...:discuss:
[h3]may be more descriptive : Computer (B) wants Computer (A) that to connect Computer (B) and read data from MSAccess mdb file and get to Computer (A) and finally send gathered data back to Computer (B)[/h3]
[h4]:discuss:May be you ask why don't use ADO connection instead of MSSQL ?
Answer is, I need to JOIN two different kind of database. I need to use MSSQL and MSAccess together with JOIN.
* MSSQL database has "Stock Materials", MSAccess database has "Business Plan". Common denominator is the "Employee ID"'s
If I wrote the software, I made them together but two different programmer has two different kind of software. My boss wants me that to combine these databases on the fly. :crazy:[/h4]
I make a mini project for this question. a sample database is available in the RAR package. (Delphi programming language)
[h2]http://www.armantr.com/files/MSAccess_via_MSSQL.rar[/h2]
This project make MDB file with standrt JET 4.0 connection and gets table names first. After that you choose one of table and wait MSSQL server gather the data from this selected MSAccess table and show to DBGrid.
May be it help for solution. Thank you for your comment.
-
What documentation did you use to configure Access as a the Linked Server?
I know that it is in the Implementation & Maintenance Microsoft Press Book but it does not go into great detail.
There are instructions in BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/164b20dd-cd51-4eed-bec9-26134e20a8ad.htm
Also there are various other examples & troubleshooting steps that you can find on the Web.
Too bad you can't migrate your Access Database to SQL Server. It is very unsecure, you have to assign permissions that enable users to delete the entire Database. :hehe:
Unless you are dealing with very small tables you will get terrible performance. Executions plans, network traffic, blocking, etc
You can still keep you Access mdb intact if you have queries and reports until you have time to migrate them.
That way you can backup & recovery the SQL Server Data Stored in tables and make a backup of you Access mdb.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
@Welsh Corgi thank you for comment.
* My third trial was in this issue was linked server. This is correct. But it's the same result with without LinkedServer type access.
I didn't use any special script for linked server. Did it manually from SQL Server 2008 manager.
Test Ok.
Details
The security subject, I gave the max permissions "everyone" user group with "Read/Write". at the firts message of this thread I detailed what I did. :discuss:
[h4]for migrating databases issuse: This is not one time job, this is on the fly. Two different kind software are running computers (A) and (B).
* MSSQL database (A) has "Stock Materials", MSAccess database (B) has "Business Plan". Common denominator is the "Employee ID"'s
I also want migrating two databases onto one and not to scratching with any this or other problems, but then I must write a new software like these two different kind of program from scratch. This is hard to make in a short time. This is not only time issue, they don't pay for it. They wants third software more but not want to change older ones. They are people like this.:crying:[/h4]
-
Hey, I'm glad that you got it to work.
Are the two programs that you are referring to Microsoft Forms, Queries, VBA Modules & Macros?
If not do you have connection strings all over the place or in a Global area or do you use a DSN?
I can understand that they do not want to pay for it.
If for instance you are using Microsoft Access Front-End. All you need to do is make a copy of the mdb after you convert the Tables to SQL Server (very easy) delete the Access Tables, then create an ODBC DSN pointing to the Server & Database. Then Link each Table. Then rename the table without the schema Name i.e. dbo.Customer to Customer. Your Application will Still work for it will be transparent to Access.
If you are using another Program such as Delphi, VB or C++, all that is needed to to change the connection strings or DSN's depending upon how the Application is written.
I did this for a Fortune 100 Company 7 years ago.
I did however take it a step further and move the VBA Code that processed incoming files via FTP or from Sybase, etc to DTS.
But the customer was still able to use their Accounting & Finance Applications until the .NET GUI was developed.
Just a thought; I understand that it may not be economically feasible from your Companies perspective but consider the risk and in Economic terms their is a value associated with the loss of revenue. Accounting is Marginal Revenue - Marginal Cost = Total Profit. Economics Subtracts the Economic Loss if you did not engage in that venture.
You could loose all of your Data. The mdb could be deleted. Users could replace the current mdb with an old one. Access Database are subject to frequent Corruption. It could take your user a lot more time for the queries to execute which is a cost. There are a number of other considerations.I have seen it all and it is not pretty.:w00t:
I would place your mdb in a share that is restricted to the Users that need Access to the Application.
You do not want them to have Full Control Permissions. The folder should be backed up regularly and it should be a Shadow Copy (trying to remember from MCSE test) so that you can restore to a point in time.
Also consider the placement of the mdb because this will impact on Network Traffic because Access is not a True Client Server Application, it is a File Server Application.
Regards,
WC
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
On another note. Have you ever seen Paul White SQL Programming Style? 😎
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Welsh Corgi (2/22/2010)
On another note. Have you ever seen Paul White SQL Programming Style? 😎Um, I have a style now? :w00t:
-
That was supposed to be a compliment.
No offense but I like your code. 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Welsh Corgi (2/22/2010)
That was supposed to be a compliment.No offense but I like your code. 🙂
I took it as such - it was just unexpected. Thank you 🙂
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply