Solved

ADODB Connection string from (Word) VBA - "Multi-step OLE DB operation generated errors..."

Posted on 2013-11-21
2
1,093 Views
Last Modified: 2013-11-23
Developing a Word (2010) Add-in that communicates with a SQL Server (2008) database.  This has worked successfully for weeks using an internal server name, and this connection string:

Set objConnection = New ADODB.Connection
objConnection.ConnectionString = "Provider=SQLOLEDB;Data Source=SERVERNAME;Initial Catalog=QualitativeDB;Integrated Security=SSPI"
objConnection.Open

Open in new window

Trying now to transition that to be a public facing address.  With help from www.connectionstrings.com, I'm trying this string.  

Set objConnection = New ADODB.Connection
objConnection.ConnectionString = "Provider=SQLOLEDB;Data Source=00.000.000.00,1433;Network Library=DBMSSOCN=TCP/IP;Initial Catalog=QualitativeDB;Integrated Security=SSPI"
objConnection.Open

Open in new window

I know I'm close because I get the "Multi-step..." SQL error message above, rather than just timing out.  I've tried it with and without the =TCP/IP, but with seems to get me closer.

Others who have asked about this error appear to be attempting some illegal SQL operation, but I'm hitting the error on objConnection.open.

Any idea how to tweak my connection string to work?  Thanks.
0
Comment
Question by:versatilebb
2 Comments
 
LVL 24

Accepted Solution

by:
DBAduck - Ben Miller earned 500 total points
ID: 39667065
The first thing I would do is to take out the ,1433 and the Network Library sections of the connection string.

Change the connection string to this:

Set objConnection = New ADODB.Connection
objConnection.ConnectionString = "Provider=SQLOLEDB.1;Data Source=00.000.000.00;Initial Catalog=QualitativeDB;Integrated Security=SSPI"
objConnection.Open 

Open in new window

0
 

Author Closing Comment

by:versatilebb
ID: 39671478
We ended up having to go with a named DNS entry instead of the IP address, for various reasons.  but thanks for the suggestion.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

770 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