How to connect Microsoft Access 2007 to SQL Server 2014?

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?
JOYV23Asked:
Who is Participating?
 
SimonConnect With a Mentor Commented:
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
 
SimonCommented:
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
 
JOYV23Author Commented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
OriNetworksCommented:
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
 
JOYV23Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Joyv23, you still have the issue?
Anyway, I think you will always need to create an ODBC connection in the client PC.
0
 
JOYV23Author Commented:
@Vitor, it's been resolved. Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.