Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to create a linked server to Access database?

Posted on 2004-11-05
14
Medium Priority
?
358 Views
Last Modified: 2008-01-09
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?
0
Comment
Question by:schlepuetz
  • 8
  • 6
14 Comments
 
LVL 27

Expert Comment

by:Dabas
ID: 12508351
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
 
LVL 3

Author Comment

by:schlepuetz
ID: 12509441
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
 
LVL 27

Expert Comment

by:Dabas
ID: 12509477
schlepuetz:
Have created the link in Exclusive mode

Dabas
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 3

Author Comment

by:schlepuetz
ID: 12509502
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
 
LVL 27

Expert Comment

by:Dabas
ID: 12509591
schlepuetz:
Probably in your ODBC. When you navigate and choose the mdb file, there is an exclusive checkbox

Dabas
0
 
LVL 3

Author Comment

by:schlepuetz
ID: 12509602
I look ed at the configuration of the ODBC connection and the exclusive box is not checked.
0
 
LVL 3

Author Comment

by:schlepuetz
ID: 12528176
Hello are you still there?
0
 
LVL 27

Expert Comment

by:Dabas
ID: 12528495
schlepuetz,
I am. Sorry, cannot help you further

Dabas
0
 
LVL 3

Author Comment

by:schlepuetz
ID: 12537764
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
 
LVL 27

Expert Comment

by:Dabas
ID: 12538824
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
 
LVL 3

Author Comment

by:schlepuetz
ID: 12544172
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
 
LVL 3

Author Comment

by:schlepuetz
ID: 12544284
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
 
LVL 3

Author Comment

by:schlepuetz
ID: 12544335
Dabas I do appreciate your help in this matter.  What would be acceptable to you as the way to close this question?
0
 
LVL 27

Accepted Solution

by:
Dabas earned 1500 total points
ID: 12546673
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question