Solved

Link SQL Server 2005 TO Access 2007 tables

Posted on 2010-09-02
7
389 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
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 500 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

713 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