How to prompt a msgbox if connection in the server fails

Question

I have here the connection string:

sConn = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=VQPBOS;Data Source=" & "GHSI" & strcode

How do I write an "If statements" to display a Msgbox if the connection fails. Because the user needs to input a "strcode" in order to have a complete server name. And the error message would be

Msgbox "Invalid Store Code. Check if the store code you have entered exists in any server"

if it cannot connect to that connection string.

I hope you get it. Thank you :)

Read this answer: [VBA data layer error handling](http://stackoverflow.com/a/4876055/138938) — Jon Crowell, Jun 17 '13 at 03:58

Our Man in Bananas · Accepted Answer · 2013-06-17T11:16:33.753

in thius case you the ADODB.Connection object would throw an error on the Connect or Execute method.

You can trap that error and handle it with the message box.

Here is an example:

dim adoCN as new adodb.connection
dim sConn as string

on error goto err_Connection

sConn ="OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=VQPBOS;Data Source=" & "GHSI" & strcode

with adoCN
    .connectionstring=sconn
    .cursorlocation=aduseclient
    .open
end with
exit sub

err_Connection:
msgbox "The Connection failed to server: " &  "GHSI" & strcode & vbcrlf & vbcrlf & "Please check your connection settings", vbokonly+vbexclamation

you can get correct Connection strings either from Connectionstrings.com or you could create a new UDL File / Microsoft Data Link, open it in Notepad, and copy the Connection string

@CarissaDeVera: if it's the answer you're looking for, please **accept it** using the green check mark :) — Our Man in Bananas, Jun 18 '13 at 08:19

How to prompt a msgbox if connection in the server fails

1 Answers1