How to test connection string?

[Hallgeir]

I have a connection string:
strConnect = "ODBC;DRIVER=SQL
Server;SERVER=w2kserver;UID=sa;PWD=sa;DATABASE=Firm"

I use this connection string to refresh my connection. But how could I test
the string to se if it's ok. I would like to have a message telling the user
that someting is wrong before my code tries to refresh the connections.

I appreciate any tips or hints!

 

[Alex White MCDBA MCSE]

Hi,

try and use the connection string like

dim adoCn as new adodb.connection
adoCn.open strConnect
if adoCn.State = adstateopen then
' it works
else
' it failed
end if

just as a side point I never store usernames and passwords in connection
strings, and if there is no way round that then don't use the SA account as
this gives anyone reading your code complete access to your SQL server.

Hope the code works....

 

[Justin Hoffman]

I have a connection string:
strConnect = "ODBC;DRIVER=SQL
Server;SERVER=w2kserver;UID=sa;PWD=sa;DATABASE=Firm"

I use this connection string to refresh my connection. But how could I
test the string to se if it's ok. I would like to have a message telling
the user that someting is wrong before my code tries to refresh the
connections.

I appreciate any tips or hints!

Are you talking about linked tables?

 

[Hallgeir]

Thanks Alex, but your code gives me an error that I don't completly
understand.
The code stops on the line: adoCn.open strConnect
And the error says sometingh like (I translate it from norwegian): "Can't
find the datasource name and there is no default driver"

Hello Justin, yes I'm talking about linked tables. I get my data from a MS
sql server 2000 database.

mvh
Hallgeir

 

[Justin Hoffman]

Thanks Alex, but your code gives me an error that I don't completly
understand.
The code stops on the line: adoCn.open strConnect
And the error says sometingh like (I translate it from norwegian): "Can't
find the datasource name and there is no default driver"

Hello Justin, yes I'm talking about linked tables. I get my data from a MS
sql server 2000 database.

mvh
Hallgeir

When you have linked odbc tables, you can always test them by running a
query in code, e.g.
"SELECT COUNT(*) As MyCount FROM MyTable"
This is fine if the linked tables work, but if they don't, you get the odbc
dialog popup which might not be what you want. For example, your users
shouldn't see this box because they work in Human Resources Department and
don't know what day of the week it is, let alone the name of the Sql Server.
In times like this, you can use ADO to help and write a function such as the
one below, which simply provides a true or false to say whether the user can
connect without showing the user any stange message.
So if you need to test the linked table, it might be better to read
information from the linked table (get the server name and database, etc)
but then use these in a function similar to the one below.


Public Function CanConnect() As Boolean

On Error GoTo Err_Handler

Dim cnn As New ADODB.Connection
Dim strConnect As String

strConnect = "Provider=sqloledb;" & _
"Data Source=w2kserver;" & _
"Initial Catalog=Firm;" & _
"User ID=sa;Password=sa"

Set cnn = New ADODB.Connection

cnn.ConnectionString = strConnect

cnn.Open

If cnn.Errors.Count = 0 Then
CanConnect = True
End If

Exit_Handler:

On Error Resume Next

If Not cnn Is Nothing Then
cnn.Close
Set cnn = Nothing
End If

Exit Function

Err_Handler:
' No Error message - just return false
Resume Exit_Handler

End Function