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
Solved

Unable to connect ADP to sqlserver 2008

Posted on 2010-11-19
6
542 Views
Last Modified: 2012-05-10
Hi Guys,

Strange one this.... I've developed and Access ADP database which connects to an ISP hosted sqlserver and it connects and works remotely with no issues. Because it is exposed to the internet I've decided to go for a dedicated server so I can make it more secure etc.
I've got the server all setup with the database and I can connect to it remotely with sql server studio manager yet when I attempt to connect to it directly with my ADP database using the same credentials I get an error message ...

Test connection failed because of an error in initializing provider. [DBNETLIB][ConnectionOpen(InvalidInstance()).]Invalid connection

Any suggestions as to why I'm getting this error message?

Thanks in advance for your participation.
0
Comment
Question by:dataflowjoe
  • 3
  • 2
6 Comments
 
LVL 84
ID: 34174185
Have you tried building a new test ADP file, and creating a new connection to the 2008 server (just to be sure you can do so)?
0
 
LVL 2

Author Comment

by:dataflowjoe
ID: 34174269
I've just done that in response to your question and I'm given the exact same message.
I'm wondering if drivers (ODBC)  are required on the server to enable a connection from an Access database? I've installed the latest MDAC drivers, not really sure if they are relevant to SQLServer

Keep the suggestions coming, I need this cracked!
0
 
LVL 39

Expert Comment

by:lcohan
ID: 34174410
Can you connect to the hosted SQL 2008 by IP in any way?  Do you have SQL Server management tools installed on the client where you test ADP file? If not you can at least install SQL 2008 driver

Installing SQL Server Native Client
http://msdn.microsoft.com/en-us/library/ms131321.aspx

More available at : http://www.microsoft.com/downloads/en/details.aspx?FamilyId=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en

Then you can check the following:

Do you need VPN to your hosted ISP where SQL is located?
local host file entry for that server name/IP
add an alias name for the SQL server name on your local client
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 84
ID: 34175933
Are your running management studio on your workstation? If not, and if you're running it on the server, make sure that you've enabled Remote Connections on the server.

Can you build a test .mdb/.accdb file and use linked tables to connect to the server? You'd have to build a DSN (Access has a utility for that) but it would tell you if it's the ADP that's giving trouble, or if it's something else.

AFAIK, you don't need to have anything installed on the server that wasn't installed during server installation. The drivers are installed on the client.
0
 
LVL 2

Accepted Solution

by:
dataflowjoe earned 0 total points
ID: 34276561
The reason for not being able to connect was because I didn't reference the full path to the server which is the IPAddress\InstanceName I just had the IP address which seems OK with SSSM but not with Access. As it is I've also gone fora fixed non standard port number to stop being bombarded with hacking bots. The full reference is [IPAddress or DSN]\[InstanceName],[PortNumber] example: 85.125.23.89\sqlexpress,5749

Thank you for your comments
0
 
LVL 2

Author Closing Comment

by:dataflowjoe
ID: 34308626
The reason for not being able to connect was because I didn't reference the full path to the server which is the IPAddress\InstanceName I just had the IP address which seems OK with SSSM but not with Access. As it is I've also gone fora fixed non standard port number to stop being bombarded with hacking bots. The full reference is [IPAddress or DSN]\[InstanceName],[PortNumber] example: 85.125.23.89\sqlexpress,5749

Thank you for your comments
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

790 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