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

MS Access ODBC or Other Connection to Remote SQL Server


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.
Simon Ball
Simon Ball
2 Solutions
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
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

  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

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


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

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

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