connect to sql

[Andre]

Excel 2000, 2003

I've built an app that connects to sql and does various things. On some
machines it works just fine, on other machines it throws an error. The
error occurs when I attempt to connect to sql. The error is:
Run-time error '-2147467259 (80004005)';
Automation error
Unspecified error


My connection code is below.
Sub Connect()

Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection
sADOConnect = "DSN=MyDSN;UID=MyUID;PWD=MyPwd"

With cn
.CursorLocation = adUseClient
.ConnectionString = sADOConnect
.ConnectionTimeout = 180
.CommandTimeout = 180
.Properties("Prompt") = adPromptCompleteRequired
.Open
End With

End Sub

Can anyone tell me what is causing this error, and how I can get around it?

Thanks, Andre

[Tim Williams]

Missing drivers?

Tim

--
Tim Williams
Palo Alto, CA


"Andre" <[email protected]> wrote in message
news:[email protected]...
> Excel 2000, 2003
>
> I've built an app that connects to sql and does various things. On some
> machines it works just fine, on other machines it throws an error. The
> error occurs when I attempt to connect to sql. The error is:
> Run-time error '-2147467259 (80004005)';
> Automation error
> Unspecified error
>
>
> My connection code is below.
> Sub Connect()
>
> Set rs = New ADODB.Recordset
> Set cn = New ADODB.Connection
> sADOConnect = "DSN=MyDSN;UID=MyUID;PWD=MyPwd"
>
> With cn
> .CursorLocation = adUseClient
> .ConnectionString = sADOConnect
> .ConnectionTimeout = 180
> .CommandTimeout = 180
> .Properties("Prompt") = adPromptCompleteRequired
> .Open
> End With
>
> End Sub
>
> Can anyone tell me what is causing this error, and how I can get around
it?
>
> Thanks, Andre
>
>

[Andre]

At first, yes. But the error persists even after installing the latest
version of MDAC.

[keepITcool]

you make yourself vulnerable to installation issues by using DSN.
If the DSN is not on the user's machine it will bomb.

Probably better to fully specify the connection string in your code
and avoid the use of DSN altogether.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Andre wrote :

> At first, yes. But the error persists even after installing the
> latest version of MDAC.

[Tim Williams]

I would second keepITcool's advice: better to go with DSN-less connection.



--
Tim Williams
Palo Alto, CA


"keepITcool" <[email protected]> wrote in message
news:[email protected]...
>
> you make yourself vulnerable to installation issues by using DSN.
> If the DSN is not on the user's machine it will bomb.
>
> Probably better to fully specify the connection string in your code
> and avoid the use of DSN altogether.
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> Andre wrote :
>
> > At first, yes. But the error persists even after installing the
> > latest version of MDAC.

[Andre]

Ok, thanks for the suggestion. Do you have any good links that show an
example of a dsn-less connection?

Thanks, Andre

[Tim Williams]

http://www.connectionstrings.com/

--
Tim Williams
Palo Alto, CA


"Andre" <[email protected]> wrote in message
news:u%[email protected]...
> Ok, thanks for the suggestion. Do you have any good links that show an
> example of a dsn-less connection?
>
> Thanks, Andre
>
>

[Andre]

What a great site - thanks. I'll give dsn-less a try and see if it resolves
my problem.

Andre

[Andre]

Dsn-less worked - at least on one computer where it had previously failed.
I'll test the others next week, but I'm very optimistic. Thanks all for
your help/suggestions.

Andre

[McHenry]

"Andre" <[email protected]> wrote in message
news:%[email protected]...
> At first, yes. But the error persists even after installing the latest
> version of MDAC.
>

I have the same problem which I cannot resolve, what drivers do I need ?

Sub Testing()
Dim cmdCommand As New ADODB.Command
Dim recSet As New ADODB.Recordset
Dim cn As New ADODB.Connection

cn.ConnectionString =
"Server=dimension9150;Database=IPS;Trusted_Connection=True"
cn.Open

cmdCommand.ActiveConnection = cn
cmdCommand.CommandText = "GetMonitors"
cmdCommand.CommandType = adCmdStoredProc

Set recSet = cmdCommand.Execute
Debug.Print recSet(0)
recSet.Close

End Sub