?
Solved

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

Posted on 2013-11-21
2
Medium Priority
?
1,231 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:Bryce Bassett
2 Comments
 
LVL 25

Accepted Solution

by:
DBAduck - Ben Miller earned 1500 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:Bryce Bassett
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
Are you looking to start a business? Do you own and operate a small company? If so, here are some courses you need to take before you hire a full-time IT staff.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

593 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