Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1211
  • Last Modified:

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

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
Bryce Bassett
Asked:
Bryce Bassett
1 Solution
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
 
Bryce BassettFreelance VBA programmerAuthor Commented:
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

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now