Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • Last Modified:

Testing a DSN

Hi guys,

I've got code which creates a DSN, and code which deletes a DSN.

I now want to be able to test a DSN's connection, eg:

Test the server exists
Test the db exists
Test the login is valid

Cheers guys!
0
Cyber-Drugs
Asked:
Cyber-Drugs
  • 13
  • 9
1 Solution
 
rockiroadsCommented:
how is your ADO knowledge?

u could try using that as that takes a DSN entry

0
 
Cyber-DrugsAuthor Commented:
I have known of ADO usage with ASP, but none with VB(A)...

Got any snippets I could modify?
0
 
rockiroadsCommented:
simple example, create reference to ADO (ActiveX Data Objects)

    Dim conn As ADODB.Connection
    Dim rsData As ADODB.Recordset
       
   
    Set conn = New ADODB.Connection
   
    conn.Open "Provider=sqloledb;" & _
         "Data Source=yourServerName;" & _
         "Initial Catalog=yourDatabaseName;" & _
         "User Id=yourUsername;" & _
         "Password=yourPassword"

    Set rsData = New ADODB.Recordset
    rsData.ActiveConnection = conn
    rsData.CursorType = adOpenKeyset

    strqry = " select * from mytable"
    sData.Open (strqry)
   
    x = rsData.RecordCount
    MsgBox "Recs Returnd : " & x
   
    rsData.Close
    Set rsData = Nothing
    conn.Close
    Set conn = Nothing
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
rockiroadsCommented:
this is a useful site

http://www.connectionstrings.com/


if u know ADO with ASP, then its more or less the same
0
 
rockiroadsCommented:
another style of dong it

Dim conn as ADODB.Connection
Set conn = New ADODB.Connection

'Establish a Connection
With conn
   .Provider = "SQLOLEDB"
   .ConnectionString = ConnectString
        Persist Security Info=False;_
        Initial Catalog=DBname;_
        Data Source=ServerName"
End With

'Open the connection
conn.Open



Its DSN day today for you then
0
 
Cyber-DrugsAuthor Commented:
It's not just DSN day for me today, it's Friday.

I don't want to think, I just want to finish work and have a few beers...

The code you've provided looks like it just makes a connection, but has nothing to do with the DSN, or am I missing something?
0
 
rockiroadsCommented:
I believe u specify the DSN in the connection name

Im off to the pub soon, but its next weekend Im looking forward to, Im off to see England vs Paraguay in Germany, leaving Friday, plenty beers one feels

0
 
Cyber-DrugsAuthor Commented:
Well the thing is, in the most recent code you posted, it asks for the DB and Server name, which is all stored in the DSN already, which is why I asked. Is there even a purpose to using a DSN if I have to respecify everything?

Cheers!
0
 
rockiroadsCommented:

try without the crap I gave before the connection

i.e.

conn.Open "DSNName", "UserID", "Password"


'Get rid of this

'Establish a Connection
With conn
   .Provider = "SQLOLEDB"
   .ConnectionString = ConnectString
        Persist Security Info=False;_
        Initial Catalog=DBname;_
        Data Source=ServerName"
End With

0
 
Cyber-DrugsAuthor Commented:
ah ok, I was looking at the lower half, hehe. :P

How would I know it has connected though? Will I just get a little message box, or will I not know?
0
 
rockiroadsCommented:
well u could check the return value

if u got error handling, note the error number

or u can try a test sql fetch

e.g. this is taken from my code that accesses Oracle (hence the name for the recordset rsOracle)
sSql is my sql select,

        Set rsOracle = New ADODB.Recordset
        rsOracle.CursorLocation = adUseClient
        rsOracle.Open sSql, m_cnOracle, adOpenStatic, adLockOptimistic, adAsyncFetch

0
 
rockiroadsCommented:
if u got no error handler, it may stop on that line (try doing it by entering an invalid DSN name or invalid userid)
0
 
Cyber-DrugsAuthor Commented:
OK, I'm not sure if I should make another Question with this one or keep it in here as it is related...

When I use this:

conn.Open "JustinDSN", "myUser", "myPass"


I get the error:

Run-time error '424':
Object required


This happens when:

DSN name is correct and username is correct
DSN name is correct and username is incorrect
DSN name is incorrect and username is correct
DSN name is incorrect and username incorrect

Now if I was getting some different error numbers, I would throw in some error handling, but they are all the same...
0
 
rockiroadsCommented:
ok, I dont have SQL Server but I have Oracle. I have created a odbc entry which links to my database using TNSNames.ORA

the DSN name is called TestDB

Public Sub TestDSN()

    Dim adoConn As ADODB.Connection
    Dim adoRecSet As ADODB.Recordset
   
   
    Set adoConn = New ADODB.Connection
    adoConn.Open "TestDB", "myuid", "mypswd"
   
    Set adoRecSet = New ADODB.Recordset
    adoRecSet.CursorLocation = adUseClient

    adoRecSet.Open "SELECT * FROM testtbl", adoConn
    Do While Not adoRecSet.EOF = True

        Debug.Print adoRecSet!field1
        adoRecSet.MoveNext
    Loop
    adoRecSet.Close
    Set adoRecSet = Nothing
    adoConn.Close
    Set adoConn = Nothing
   
End Sub



Now this has successfully made a connection

Does your DSN point to the right sql server?

0
 
rockiroadsCommented:
A good way to test is if u use Access to create linked tables to your DSN. Then try to open one of these tables.
This then proves whether there is an issue with the code or the DSN
0
 
Cyber-DrugsAuthor Commented:
I used your code, and it returned records, which is great, i Just need it to give me a msg box for when it successfully connects, and then it can disconnect. Would doing a query to return a list of all the tables in the database, and if the number is equal to or higher than one, then it passes, elsewise it fails, or would you call that a bad method?
0
 
Cyber-DrugsAuthor Commented:
Also, in MSSQL, normally I use this to return a list of all the tables:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

but when I put that code inside the VBA Code, I get this error:

Run-time error '3265':
Item cannot be found in the collection corresponding to the requested name or ordinal.



Do I need to change that to work in your code?

Hopefully I can finish this off today, as I'm off for a music festival after work today, so it would be nice to leave with a clean slate, haha.

Cheers!
0
 
Cyber-DrugsAuthor Commented:
OK, I have it half working...

Private Sub Command0_Click()
    Dim adoConn As ADODB.Connection
    Dim adoRecSet As ADODB.Recordset
   
    Set adoConn = New ADODB.Connection
    adoConn.Open "JustinDSN", "myuser", "mypass"
   
    If adoConn.State = 1 Then
        MsgBox "Connection Successful"
    Else
        MsgBox "Connection Un-Successful"
    End If
   
    adoConn.Close
    Set adoConn = Nothing
End Sub



Now if the server cannot be found, I get the error:


Run-time error '-2147467259 (800004005)':
[Microsoft][ODBC SQL Server Driver][SQL Server]SQL Server has been paused. No new connections will be allowed.


How can I change this to:

MsgBox "Cannot connect to server"

?




Cheers!
0
 
Cyber-DrugsAuthor Commented:
Ignore me, I was only packing to try finish before I left for my festival...

Here is the working code:

Private Sub Command0_Click()
    Dim adoConn As ADODB.Connection
    Dim adoRecSet As ADODB.Recordset
   
    On Error GoTo ConnectError
        Set adoConn = New ADODB.Connection
        adoConn.Open "JustinDSN", "myuser", "mypass"
    Exit Sub
   
ConnectError:
        MsgBox "Could not connect to Server"
    Exit Sub
   
    If adoConn.State = 1 Then
        MsgBox "Connection Successful"
    Else
        MsgBox "Connection Un-Successful"
    End If
   
    adoConn.Close
    Set adoConn = Nothing
End Sub


Thanks rockiroads, enjoy the football game this weekend, I'll probably catch you again next week!
0
 
rockiroadsCommented:
Hi Cyber Drugs/Justin?

Sorry I missed all your posts today

Looks like you managed to sort it without my help
0
 
rockiroadsCommented:
What festival is this then
And yes, I will definitely make the most of this weekend.
0
 
rockiroadsCommented:
hmm, I got to get a new set of glasses

a music festival

sounds good

two gigs yesterday, certainly Bon Jovi and I think Guns and Roses as well
Last one I went to was years ago. Donnington. Brilliant day. Iron Maiden were headlining. Fab act them

Enjoy...
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 13
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now