• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

MS Access ODBC or Other Connection to Remote SQL Server

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
Simon Ball
Asked:
Simon Ball
2 Solutions
 
als315Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  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
 
Simon BallAuthor Commented:
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
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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now