Word to SQL 2000 Connection Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Jost - 10 Feb 2005 19:51 GMT

Hi experts. I'm having problems with Word 2003 connecting to our MS-SQL 2000
servers. In the first "with con" statement, it works fine if I hard code the
information. However if I try to use variables to build the connection, I
get "Run Time Error -2147217843 (80040e4d) Invalid String Attribute"

' Works fine - hard coded
With con
.Provider = "SQLOLEDB"
.Open "Server=devsvr01; Database=dev; Trusted_Connection= yes; ID=abc;
PWD=123"
End With

' build statement from variables

lsServer="devsvr01"
lsDB="dev"
lsID = "abc"
lsPwd = 123"

With con
.Provider = "SQLOLEDB"
.Open "Server=" + lsServer + "; Database=" + lsDB + ";
Trusted_Connection=yes; ID=" + lsID + "; PWD=" + lsPwd
End With

I think the problem is it is looking for a closing quote, but how do I do
that? Would this be solved if I could run a stored procedure instead of
building the sql in my code?

Reply to this Message

Jezebel - 15 Feb 2005 00:50 GMT

I think you're not quite clear on how you build strings in VB. In
particular, you need to be using the & operator to concatenate the elements.
In this case, I suggest you build the entire argument into a string, then
use that --

Dim pArgString as string

lsServer="devsvr01"
lsDB="dev"
lsID = "abc"
lsPwd = 123"
pArgString = "Server=" & lsServer & "; Database=" & lsDB & ";
Trusted_Connection= yes; ID=" & lsID & "; PWD=" & lsPwd

Debug.Print pArgString <<<<< check what it looks like

With con
.Provider = "SQLOLEDB"
.Open pArgString

> Hi experts. I'm having problems with Word 2003 connecting to our MS-SQL
> 2000
[quoted text clipped - 26 lines]
> that? Would this be solved if I could run a stored procedure instead of
> building the sql in my code?

Reply to this Message

John Jost - 15 Feb 2005 13:57 GMT

Excuse my ignorance, I'm self taught. What is the difference in using the
'&' or the '+' ? I thought both were interchangeable. If there is a
difference I would like to understand the differences.

Thank you for your code sample I will give that I try today. I am sure it
will help.

> I think you're not quite clear on how you build strings in VB. In
> particular, you need to be using the & operator to concatenate the elements.
[quoted text clipped - 46 lines]
> > that? Would this be solved if I could run a stored procedure instead of
> > building the sql in my code?

Reply to this Message

Jezebel - 15 Feb 2005 21:07 GMT

The problem with using + for concatenation is that you're never sure what
you'll end up with. "2" + "3" will give you "23" or 5 depending on context
in unpredictable ways. Even if you don't use variants as data types, VBA
often does.

I'm actually not sure if that was you're problem here -- maybe there's just
a missing quote somewhere.

> Excuse my ignorance, I'm self taught. What is the difference in using the
> '&' or the '+' ? I thought both were interchangeable. If there is a
[quoted text clipped - 60 lines]
>> > of
>> > building the sql in my code?

Reply to this Message

Rate this thread: