• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

Link SQL Server 2005 TO Access 2007 tables

1. I am a newbie to SQL Server - and a humbled Access user.
2. Ideally, I would like to connect our Local SQL server to a remote (WAN)server but I don't know how and I don't know if I have the permissions.
3. I can link to the remote server from Access using ODBC so I have the tables in Access.
4. How do I link the SQL server to the Access tables?
5. I am willing to try step #2 since that would be most efficient.
6. Saw the following in a dead thread:

If that is the case, then it is very simple.
Go to SSMS.

SSMS (SQL Server Management Studio -> Expand Server Objects
        -Right Click Linked Servers -> Select New Linked Server
On the General Page:
        -Linked Server: Type the Name for your Linked Server
        -Server Type: Select Other Data Source
        -Provider: Select Microsoft OLE DB Provider for ODBC Drivers
        -Product name: Type MySQLDatabase
        -Data Source: Type the name of the DSN you created
On The Security Page
        -Map a login to the Remote User and provide the Remote Users Password
        -Click Add under Local server login to remote server login mappings:
        -Select a Local Login From the drop down box
        -Type the name of the Remote User
        -Type the password for the Remote User

That's it..

0
Heartless91
Asked:
Heartless91
  • 4
  • 3
1 Solution
 
EmesCommented:
you can use linked server command is sql

exec sp_addlinkedserver @server='Access',
@srvproduct='Access',
@provider='Microsoft.Jet.OLEDB.4.0',
@datasrc='c:\test.mdb'

then run
sp_tableex N'Access'

to test it.
0
 
Heartless91Author Commented:
I need you to walk me through the steps. I open up SSMS and add the code... where...

Thanks
0
 
EmesCommented:
open a query window and place the code there that is where all the queries should be run


exam.gif
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Heartless91Author Commented:
I'm working on it with our Tech Director. I'll get back tomorrow morning. Thanks.
0
 
Heartless91Author Commented:
Good Morning.

Here is some information:
I am wanting to create databases for end users with live data but I want to protect those tables. My thinking is to "move" the tables to our local sql server and create databases linked to the server and not the remote site. It would get it's real-time data from a secured Access database that is linked to the WAN server. Yes, ideally, I would like to connect server-to-server but there are some obstacles. So, I've created an Access database that gets the data. I want the sql server to link to the Access database. Then I want to create Access databases that link to the local sql server.
Here are some questions.
1. If I follow your steps with the code you've provided, will that create tables on the sql server that I can link to?
2. Will the data be live or will I have to schedule the code to run to get 15 minute snapshots?

Thanks.
Newbie
0
 
EmesCommented:
by creating the linked server all you are doing is connecting the access tables to SQL server.  You use sql code to get the data but no tables are created in sql server.

You can have access link the tables in and set the connection to sql server to have read only rights. that way you have you data safe and do not need to have updates.

0
 
Heartless91Author Commented:
I appreciate the help. I just have the experience/knowledge to move forward with this - yet.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now