Testing a DSN

how is your ADO knowledge?

u could try using that as that takes a DSN entry

I have known of ADO usage with ASP, but none with VB(A)...

Got any snippets I could modify?

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

this is a useful site

http://www.connectionstrings.com/


if u know ADO with ASP, then its more or less the same

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

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?

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

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!

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

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?

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

if u got no error handler, it may stop on that line (try doing it by entering an invalid DSN name or invalid userid)

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...

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

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?

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!

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!

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!

Hi Cyber Drugs/Justin?

Sorry I missed all your posts today

Looks like you managed to sort it without my help

What festival is this then
And yes, I will definitely make the most of this weekend.

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...