Solved

Unable to connect ADP to sqlserver 2008

Posted on 2010-11-19
6
545 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
[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
  • 2
6 Comments
 
LVL 85
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 40

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

726 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