Solved

How to connect Microsoft Access 2007 to SQL Server 2014?

Posted on 2015-02-18
7
694 Views
Last Modified: 2015-02-25
I have an SQL Server 2014. I created a Linked server from Pervasive. How can I see the tables of the Linked Server in Microsoft Access 2007?
0
Comment
Question by:JOYV23
7 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40617745
I don't think you can. The only way to get data from them would be a passthru query that executes a stored procedure on the SQL Server and returns records.
0
 

Author Comment

by:JOYV23
ID: 40617757
Hello Simon. So how can I connect a new database created in SQL to talk to the Linked Server within it? I just want to pull records from the Linked Server to Access.
0
 
LVL 17

Expert Comment

by:OriNetworks
ID: 40617761
I'm not sure what Pervasive is but in the past I have done this through ODBC connections. I would create an ODBC connection to the sql server on the local machine that will run the MS Access front end. I can then import certain tables and link any table in any query as if it was local to the database.  I can manage the linked tables in MS Access using Database Tools->Linked Table Manager
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40617767
1. Create a stored procedure in SQL that does a SELECT from the linked server (using either 4 part notation or the Openquery method)
2. Define a passthru query in the Access database that executes the stored proc.

Or
1. take a snapshot the data from the linked server into a table in the SQL server.
2. Then you can use the resulting table as a linked table from Access.

The Wiki page for Pervasive suggests that it does offer various drivers (ADO.NET; ODBC v3.51 and JDBC 2 for Core, Level 1, and Level 2; and OLE DB), so you may be able to access it directly, rather than via SQL Server, as OriNetworks suggests, if it is on your LAN.
0
 

Author Comment

by:JOYV23
ID: 40618951
Simon, the database is on the server. Is it possible to connect on access on a client computer without creating an ODBC connection in the client pc?
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40630719
Joyv23, you still have the issue?
Anyway, I think you will always need to create an ODBC connection in the client PC.
0
 

Author Comment

by:JOYV23
ID: 40630749
@Vitor, it's been resolved. Thank you
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 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