Testing a connection to see if it works

[Dimension]

I have a connection string i want to test to see if its valid...meaning it can connect to a db. How is this done?

[MetallicaD]

here is what I do to make and test connection strings. create a new textfile.. rename it to <anything>.UDL Then open up the file and set your connection properties (choose providor, point to your db) then there is a button called labeled 'Test Connection'. This will, obviously, test your connection.

Now, here is the cool part.. open up notepad.. drag this udl file and drop it into notepad.. this will write out your connection string

-mcd

[Dimension]

i've already done all of that...i just want a way to see if the connectionstring supplied is valid or not

[Dimension]

^ without having to hit the test connection....in code

[Steve Stunning]

I also had the same questions and this is what I deemed to do the trick.



Function VerifyConnection(ConnectionString As String) As Boolean

On Error GoTo ErrorHandler

Dim Cn As New ADODB.Connection

Cn.CursorLocation = adUseClient
Cn.ConnectionString = ConnectionString
Cn.ConnectionTimeout = 3


' open connection
Cn.Open

' Close connection
Cn.Close


ErrorHandler:

If Err.Number <> 0 Then
VerifyConnection = False
else
VerifyConnection = True
end if

End Function

[fasi]

You can also use the following code to test the connection say cnn is your ado connection

you can use

if cnn.state <> adStateOpen then
    msgbox"Cannot open the connection"
endif