Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS Access ODBC or Other Connection to Remote SQL Server

Posted on 2011-09-12
3
Medium Priority
?
345 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

597 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