schlepuetz
asked on
How to create a linked server to Access database?
On our production server (SQL Server 7.0) we have a linked server that uses an ODBC connection to connect to an Access database which has a linked table which points to another access database.
We have many users with .mde front ends using SQL Server for the back end.
There is a view that uses the linked server for it's data source. The view is used to populate dropdown lists in the front ends so the users can select their employee names.
The problem I am having is I am trying to create the same setup on a test server and I can not get it to work.
The production server does not create any lock files on the Access databases.
When I set up the same thing on the test server there is a lock file created when I set up a linked server.
How do I set up a linked server so that there is no lock file created on the Access database that it is connecting to?
We have many users with .mde front ends using SQL Server for the back end.
There is a view that uses the linked server for it's data source. The view is used to populate dropdown lists in the front ends so the users can select their employee names.
The problem I am having is I am trying to create the same setup on a test server and I can not get it to work.
The production server does not create any lock files on the Access databases.
When I set up the same thing on the test server there is a lock file created when I set up a linked server.
How do I set up a linked server so that there is no lock file created on the Access database that it is connecting to?
ASKER
Hello Dadas,
Yes I do mean .ldb file.
On our production server we have a linked server that is pointing to an Access database.
This linked server does not create any lock files on the Access database but any linked server that I create makes a lock file.
When this lock file is created then no other user can use the view that is created off of the linked server.
Yes I do mean .ldb file.
On our production server we have a linked server that is pointing to an Access database.
This linked server does not create any lock files on the Access database but any linked server that I create makes a lock file.
When this lock file is created then no other user can use the view that is created off of the linked server.
schlepuetz:
Have created the link in Exclusive mode
Dabas
Have created the link in Exclusive mode
Dabas
ASKER
What is Exclusive mode and how do I create a linked server so that it is not created in Exclusive mode?
By changing this mode will it not create a lock file when it is used?
By changing this mode will it not create a lock file when it is used?
schlepuetz:
Probably in your ODBC. When you navigate and choose the mdb file, there is an exclusive checkbox
Dabas
Probably in your ODBC. When you navigate and choose the mdb file, there is an exclusive checkbox
Dabas
ASKER
I look ed at the configuration of the ODBC connection and the exclusive box is not checked.
ASKER
Hello are you still there?
schlepuetz,
I am. Sorry, cannot help you further
Dabas
I am. Sorry, cannot help you further
Dabas
ASKER
I have switched tactics and am now trying to find a solution so that when we upgrade our production server to SQL Server2000 we do not have a problem trying to retrieve the information from the data base.
I would like some help trying to find a solution.
I have set up two databases on the file server one is called link the other is source. Link has a linked table in it that points to a table in source. I have created a linked server that points directly at the data base called link. I have also created a view based on the linked server. The view works fine but if I try and acces the view from any other pc it tells me that I can not. I get the following error--
ODBC call failed
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/db provider returned message: The Microsoft Jet Database engine cannot be open the file 'file name'. It is already opened exclusively by another uer, or you need permissions to view its data.](#7312)[Microsoft][O
I would like some help trying to find a solution.
I have set up two databases on the file server one is called link the other is source. Link has a linked table in it that points to a table in source. I have created a linked server that points directly at the data base called link. I have also created a view based on the linked server. The view works fine but if I try and acces the view from any other pc it tells me that I can not. I get the following error--
ODBC call failed
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/db provider returned message: The Microsoft Jet Database engine cannot be open the file 'file name'. It is already opened exclusively by another uer, or you need permissions to view its data.](#7312)[Microsoft][O
schlepuetz,
I have had similar problems on my system.
My experience is that with mdb files, it is not just a matter of having proper security to the database, but also the kind of security you have to access the server on which the mdb file resides. Probably the connection is trying to log into the server as guest.
I have not been able to completely overcome this problem if the mdb file is not on the same server.
Hope this helps
Dabas
I have had similar problems on my system.
My experience is that with mdb files, it is not just a matter of having proper security to the database, but also the kind of security you have to access the server on which the mdb file resides. Probably the connection is trying to log into the server as guest.
I have not been able to completely overcome this problem if the mdb file is not on the same server.
Hope this helps
Dabas
ASKER
Both the logins for the test and production server are domain admins. How else could the the security be set it would seems to me that if they are both domain admins then the security would be the same
ASKER
Well I have given up trying to do this with a linked server.
What I am doing now is using the DTS to do a scheduled transfer of the data into a table on SQL Server and then from there I will create a view that all can use.
What I am doing now is using the DTS to do a scheduled transfer of the data into a table on SQL Server and then from there I will create a view that all can use.
ASKER
Dabas I do appreciate your help in this matter. What would be acceptable to you as the way to close this question?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Under lock file do you mean ldb file?
ldb files get created whenever anybody is using the mdb. It would suggest that somehow or other your application is opening a table without closing it properly.
I do not think that your problem is related to the lock file. What is it that is not working?
Dabas