Solved

How to connect Microsoft Access 2007 to SQL Server 2014?

Posted on 2015-02-18
7
705 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 48

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

840 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