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

Heartless91Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Heartless91Author Commented:
I appreciate the help. I just have the experience/knowledge to move forward with this - yet.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

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.