Solved

Link SQL Server 2005 TO Access 2007 tables

Posted on 2010-09-02
7
385 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
  • 4
  • 3
7 Comments
 
LVL 14

Expert Comment

by:Emes
Comment Utility
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
Comment Utility
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
Comment Utility
open a query window and place the code there that is where all the queries should be run


exam.gif
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

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

Author Comment

by:Heartless91
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
I appreciate the help. I just have the experience/knowledge to move forward with this - yet.
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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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.

772 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

9 Experts available now in Live!

Get 1:1 Help Now