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?
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.

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.
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.
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.
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

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
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?
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.
JOYV23Author Commented:
@Vitor, it's been resolved. Thank you
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.