Solved

Unable to connect ADP to sqlserver 2008

Posted on 2010-11-19
6
539 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

919 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now