[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Link SQL Server 2005 TO Access 2007 tables

Posted on 2010-09-02
7
Medium Priority
?
394 Views
Last Modified: 2012-05-10
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
Comment
Question by:Heartless91
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 14

Expert Comment

by:Emes
ID: 33588738
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
 

Author Comment

by:Heartless91
ID: 33589196
I need you to walk me through the steps. I open up SSMS and add the code... where...

Thanks
0
 
LVL 14

Expert Comment

by:Emes
ID: 33589591
open a query window and place the code there that is where all the queries should be run


exam.gif
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Heartless91
ID: 33590288
I'm working on it with our Tech Director. I'll get back tomorrow morning. Thanks.
0
 

Author Comment

by:Heartless91
ID: 33595991
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
 
LVL 14

Accepted Solution

by:
Emes earned 2000 total points
ID: 33598433
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
 

Author Closing Comment

by:Heartless91
ID: 33598881
I appreciate the help. I just have the experience/knowledge to move forward with this - yet.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

650 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