Can connect in VB but not VBA!

Hello,
We are experiencing a frustrating problem where we can connect to an interna
l
SQL Server via any application EXCEPT Office apps (Excel, Word, etc.) using
VBA. The error is:
[DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or a
ccess
denied.
We're using very elementary code, that succeeds in VB programs, for example,
but fails in Excel VBA. The code is:
on error resume next
Dim conn As ADODB.Connection
Dim strw As String
Set conn = New ADODB.Connection
strw = "Provider=sqloledb;User ID=the_user;Password=the_password;" & _
"Data Source=192.168.0.8;Initial Catalog=the_catalog;Network
Library=DBMSSOCN;"
' errors out here
conn.Open strw
If Err.Number = 0 Then
MsgBox "seems alright."
Else
MsgBox "error: " & Err.Description
End If
conn.close
set conn = nothing
We're stumped. We've never heard of any ADODB VB code, that works perfectly
fine in a VB application, that suddenly stops working when ported to Excel
VBA. We've tried specifying the port, changing the Network Library, etc., as
well...all sorts of things.
Help!I would guess there is still some difference some way in how
you are connecting in VB apps and how you are connecting in
VBA - especially if you always connect fine from VB to the
same server from the same PC and using the exact same code
in VBA to the same server from the same PC does not work.
And of course this would imply that the VB app and the VBA
app have the exact same references in the exact same order.
It's doesn't sound like it's really a SQL Server issue but
you could investigate the possibilities listed in this
article:
Potential causes of the "SQL Server does not exist or access
denied" error message
http://support.microsoft.com/?id=328306
-Sue
On Thu, 07 Sep 2006 05:30:26 GMT, "brianUE" <u26284@.uwe>
wrote:

>Hello,
>We are experiencing a frustrating problem where we can connect to an intern
al
>SQL Server via any application EXCEPT Office apps (Excel, Word, etc.) using
>VBA. The error is:
> [DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist
or access
>denied.
>We're using very elementary code, that succeeds in VB programs, for example
,
>but fails in Excel VBA. The code is:
> on error resume next
> Dim conn As ADODB.Connection
> Dim strw As String
> Set conn = New ADODB.Connection
> strw = "Provider=sqloledb;User ID=the_user;Password=the_password;" & _
> "Data Source=192.168.0.8;Initial Catalog=the_catalog;Network
>Library=DBMSSOCN;"
> ' errors out here
> conn.Open strw
> If Err.Number = 0 Then
> MsgBox "seems alright."
> Else
> MsgBox "error: " & Err.Description
> End If
> conn.close
> set conn = nothing
> We're stumped. We've never heard of any ADODB VB code, that works perfect
ly
>fine in a VB application, that suddenly stops working when ported to Excel
>VBA. We've tried specifying the port, changing the Network Library, etc., a
s
>well...all sorts of things.
>Help!