?
Solved

MS Access ODBC or Other Connection to Remote SQL Server

Posted on 2011-09-12
3
Medium Priority
?
340 Views
Last Modified: 2012-05-12
Hello,  

I was used to working with access accross my old employers network which made odbc very easy.  Now i need to provide an access mdb which connects (odbc or dsnless) to a table on a remote server in a different country.

I can access the server using MSTSC to login, and have created a view, and a user with select access to the view...

My access mdb is simple.. the front end needs to connect to a view..... i have written the rest of the code using a flat file imported in access, but now i need to replace it with a connection to the db server...

And i have realised i have no idea how to go about this.

What do i need to do to get this accdb to connect to the remote servers IP ( and log in using the windows username and password i have been given, and then conect and retrive the view in odbc using a different sql username and password.

I can re-write my code using ADO if necessary.

This is quite urgent.
0
Comment
Question by:Simon Ball
[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
3 Comments
 
LVL 40

Assisted Solution

by:als315
als315 earned 800 total points
ID: 36522139
Usually you will need some kind of VPN connection to your server (if it is not opened to internet). If you have it - use server IP address as server name in ODBC connection
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1200 total points
ID: 36522272

  If your using DAO, then the simplest is to work with the table as a linked table.   A linked table appears in the tabledef's collection and it it the .Connect property of the tabledef object that tells Access how to reach out to the table.

  That connect string can use a DSN or not.   My suggestion would be to first connect to the DB and the view using a DSN.

  That gets you a connection outside of Access that you can use to connect (and test).  Then within Access, connect to the view as a linked table (and test).  Finially, you can use the code here:

Using DSN-Less Connections
http://www.accessmvp.com/DJSteele/DSNLessLinks.html

  To convert the DSN parameters into the tabledef object, making the DSN un-needed.

Jim.

0
 
LVL 15

Author Closing Comment

by:Simon Ball
ID: 36534481
I used team viewer to log in to a PC on the network, then ODBC and a linked table.

So a combination of the above.

The server was not visible so i was never going to be able to connect to it remotely without a VPN.

The system worked on the test machine, and i have some VBA code for making ODBC on the fly in the mdb, but i will also look at the dsn connection link posted above.
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

770 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