connect to sql
 

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

connect to sql
 

Missing drivers?

Tim

--
Tim Williams
Palo Alto, CA


"Andre" wrote in message
...
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

connect to sql
 

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

connect to sql
 

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.

connect to sql
 

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



--
Tim Williams
Palo Alto, CA


"keepITcool" wrote in message
. com...

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.

connect to sql
 

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

Thanks, Andre

connect to sql
 

http://www.connectionstrings.com/

--
Tim Williams
Palo Alto, CA


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

Thanks, Andre

connect to sql
 

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

Andre

connect to sql
 

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

connect to sql
 

"Andre" wrote in message
...
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_Connect ion=True"
cn.Open

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

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

End Sub