?
Solved

Linked Servers / Access not working

Posted on 2009-12-17
12
Medium Priority
?
162 Views
Last Modified: 2012-05-08
I'm running SQL Server 2005 Express.
I attached an Access mdb.
It shows it to me under linked servers.
When I do a simple Select * From AccessServer...Tbl I get a message Executing Query but nothing happends. The table is only 25 rows and this went on for an hour before finaly cancelling. And when I pressed Cancel Executing Query it went on for a while until I finaly shut down server management.
0
Comment
Question by:Jess31
  • 5
  • 4
  • 3
12 Comments
 
LVL 61

Expert Comment

by:HainKurt
ID: 26075455
can you browse the linked db?

I have added mdb to my sqlexpress, I can browse and cen select it by

select * from EE...HainKurt

where EE is linked Server name to mdb file
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26075465
Have you tried using OPENQUERY?
Check this link for details:
http://msdn.microsoft.com/en-us/library/aa276848(SQL.80).aspx 

select * from OPENQUERY(AccessServer, 'select * from yourtableinaccess')

Open in new window

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26075468
try to recreate the linked server, I used

Microsft Jet 4.0 OLE DB Provider
EE
path to mdb
empty for the rest fields when creating it...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:Jess31
ID: 26075571
what is EE?
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26075665
Linked server name & product name
I put "EE" for both
0
 
LVL 1

Author Comment

by:Jess31
ID: 26075860
I created a new linked db, called it EE as you did.
Then I tried
select * from OPENQUERY(EE, 'select * from warehouses')
and some error relating to security, and I changed in the security tab checking off this:
"be made using logins current security context"
then I ran it again and it just gave me the Executiing Query msg for too long (the table is only 20 records!)
So I broke it and got this:
OLE DB provider "SQLNCLI" for linked server "EE" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "EE" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26076103
How are you creating the the linked server? try dropping it and re-creating it like this

exec sp_addlinkedserver @server='YourAccessServer',
@srvproduct='Access',
@provider='Microsoft.Jet.OLEDB.4.0',
@datasrc='c:\test.mdb'

Open in new window

0
 
LVL 1

Author Comment

by:Jess31
ID: 26076189
ralmada,
I this worked for two times, after trying a select for third time it does work anymore.

I should point out this is exactly what happened before. It had also worked for about 2 times and then would not work anymore.
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26076295
do you have any password on mdb file?
0
 
LVL 1

Author Comment

by:Jess31
ID: 26076782
No password on the mdb
0
 
LVL 61

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 26077265
create a new mdb file, create a table, put some records into it
create another link to new mdb and run a test query...
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26091418
Per the last error message >>SQL Server does not allow remote connections<<
Have you made sure that you have enable it?
http://msdn.microsoft.com/en-us/library/dd857537(VS.85).aspx
Also where is the mdb file? is it on a different domain/network drive? Make sure the SQL Service account running your server instance has access to that domain.
To check what is the service account, go to SQL SERVER Configuration Manager. click on SQL SERVER 2005 Services in the left pane. On the right pane you will see 5-6 services running like Intergration Service, Analysis Service, Reporting Service, and so on. One of them was SQL SERVER(MSSQLSERVER), right click on it-> Properties. If you are running with the Local System account and the mdb file is on another server, you might need to change the account to a domain account with access to that network resource.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

839 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