Solved

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

Posted on 2013-11-21

1 solution

1,138 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.

Comment

Question by:Bryce Bassett

[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

Join The Community

2 Comments

LVL 24

Accepted Solution

by:DBAduck - Ben Miller

DBAduck - Ben Miller earned 500 total points

ID: 396670652013-11-21

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

Author Closing Comment

by:Bryce Bassett

ID: 396714782013-11-23

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

Featured Post

Question has a verified solution.

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

Windows 10 One Year Later

Article by: John

My experience with Windows 10 over a one year period and suggestions for smooth operation

Deleting Shapes - A Warning

Article by: Martin

This article describes a serious pitfall that can happen when deleting shapes using VBA.

Using SmartArt to Create Charts

Video by: Tony

The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.

Create a Query and Grouped Report and Modify Design using Access

Video by: crystal

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…