Check if user has odbc setting

[DrSmyth]

Hi, I'm utilising system dsn ODBC settings to distribute some reports to my users. The problem i have is that i need to check to see if a particular system dsn is present before the data is refreshed. Does anybody have any code/advise on how to do this?

Thanks

 

[Ed2020]

The following code will create the DSN for you If it already exists it should just be overwritten. Does this help?

DBEngine.RegisterDatabase "DSN Goes Here", "Oracle73 Ver 2.5", True, "Server=ServerNameGoesHere" & vbCr & "UserID=UserIDGoesHere"

Please do not feed the trolls.....

 

[Ed2020]

Actually I'm wrong, a warning message is displayed if the DSN already exists.

The best way would be to trap the runtime error that occurs if the DSN doesn't exist and run the code I posted previously.

Ed Metcalfe.

Please do not feed the trolls.....

 

[DrSmyth]

Hi, thanks for the response. I've been having trouble trapping the runtime error that occurs as i don't think that one is generated. Basically i stuck a generic 'on error goto' at the beginning of the module, but this isn't being picked up.

Because Excel simply prompts the user to select a datasource if the odbc setting is not already on the machine i believe that this is not generating an error. I tried recording this process (after deleting the ODBC system from my own PC) and was left with a blank module..

Thanks again for the response though....

P.S.

Please do not feed the trolls.....

... What you got against Trolls?

 

[Ed2020]

DrSmyth,

If you can post the code you have I'll try and figure it out for you...

Ed Metcalfe.

Please do not feed the trolls.....

 

[DrSmyth]

Cheers Ed, it's quite simple code because all i'm doing is refreshing some pivots and tables...

Application.DisplayAlerts = False
ActiveWorkbook.RefreshAll
Application.DisplayAlerts = True

not really much to go on i'm afraid (have tried commenting out the display alerts line as well)

 

[Ed2020]

Hmmm.... I shall have to have a think about this one. I'll get back to you tomorrow.

Ed Metcalfe

Please do not feed the trolls.....

 

[Ed2020]

DrSmyth,

Have a look at this, I think it will do what you want:

http://www.enterpriseitplanet.com/resources/scripts_win/article.php/3089341

Ed Metcalfe.

Please do not feed the trolls.....

 

[DrSmyth]

Cheers Ed, I'll have a play with the code they suggest...

Thanks for the pointer, I'll let you know how i get on..