Solved

MS Access ODBC or Other Connection to Remote SQL Server

Posted on 2011-09-12
3
338 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 200 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 300 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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

690 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