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?
LVL 3
schlepuetzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DabasCommented:
Hi schlepuetz:
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
0
schlepuetzAuthor Commented:
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.
0
DabasCommented:
schlepuetz:
Have created the link in Exclusive mode

Dabas
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

schlepuetzAuthor Commented:
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?
0
DabasCommented:
schlepuetz:
Probably in your ODBC. When you navigate and choose the mdb file, there is an exclusive checkbox

Dabas
0
schlepuetzAuthor Commented:
I look ed at the configuration of the ODBC connection and the exclusive box is not checked.
0
schlepuetzAuthor Commented:
Hello are you still there?
0
DabasCommented:
schlepuetz,
I am. Sorry, cannot help you further

Dabas
0
schlepuetzAuthor Commented:
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][ODBC SQL Server Driver][SQL Server]OLE DB provider'Microsoft.Jet.OLEDB.4.0' reprtoed an error. (#7399)

0
DabasCommented:
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
0
schlepuetzAuthor Commented:
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
0
schlepuetzAuthor Commented:
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.
0
schlepuetzAuthor Commented:
Dabas I do appreciate your help in this matter.  What would be acceptable to you as the way to close this question?
0
DabasCommented:
schlepuetz:
If you do not mind, just accept any of my answers with a B grade.
Alternatively you can post at CS asking for a refund.

I have also opted for the DTS package and scheduled transfer solution. Or for my programs to simultaneously update the mdb and the SQL server. My policy is to slowly move everything out of jet and into sql.
The benefits of the speed in retrieval of data from SQL plus the ability to write stored procedures to do server side operations highly outweigh the slight inconvenience.

Good luck!

Dabas
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.