Still stuck-can you trap SQL 7 errors in Excel? 1

[j9]

My Excel worksheet contains several dataranges that each use a trusted connection. It works great when the NT user is allowed access to the database. BUT, if an NT user is not allowed access to the database: as soon as querytable(1).refresh executes, a Microsoft SQL Server Login error box pops up saying 'Connection failed:, etc...'.
I tried to handle this error with 'On Error' code, but apparently it's not recognized as an error because it never gets trapped. I guess it's a SQL 7 provider error and not an Excel error. So, is there a way to trap this type of error so that I can control what happens if a user is or isn't allowed access? If so, how?

 

[ide]

You use Microsoft Query, I presume.
The result set is automatically refreshed in Query, before the Returning External Data to Microsoft Excel dialog box is displayed, even when the Auto Query button is not pressed in. If you want to prevent Query from running the query before you return the data to Excel, click Options on the Edit menu in Query, and then clear the Validate queries before saving or returning data check box. MS Excel help
Disable auto refreshing, and create an auto_open macro, to check the user rights, and refresh the queries if has permission to connect to.
fe. (ADO) :
'create a connection and check error
Sub auto_open()
On Error GoTo dsbleRefresh
Dim cnn As New ADODB.Connection
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=SQLOLEDB..."
cnn.Open
'insert code to refresh queries; querytable(1).refresh
Exit Sub
dsbleRefresh:
MsgBox "You havn't permission to refresh"
End Sub

i hope it works
ide

 

[ide]

grrr!

cnn.ConnectionString = "Provider=SQLOLEDB..."

and not lol

 

[ide]

hopeless...

 

[j9]

All set--Thank you!