Solved

How to connect Microsoft Access 2007 to SQL Server 2014?

Posted on 2015-02-18
7
664 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:SimonAdept
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 18

Accepted Solution

by:
SimonAdept 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 45

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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 Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now