test dsn connection 1

[sramey00]

i got this vb6 app i want to error test the connection.
if the odbc dsn exists execute, if it doesn't then allow the option to create it.

however, i dont know which functions to use, any suggestions?

Steve

everyday something new is learned, hmmm...

 

[bjd4jc]

You can start by looking at faq222-273

 

[bjd4jc]

and also faq222-82

You can also do a search on the forum as this has been discussed many a times.

 

[sramey00]

hah.. the 1 place i forgot to look this time and it ends up being there... thanks!

Steve

everyday something new is learned, hmmm...

 

[sramey00]

i was a little unclear in my first post.
i already have code to create the dsn connection.
right now i want the vb app to start and during form load check to see if the dsn is included in the odbc.

during form load my app connects to the database and if that dsn isn't present, the app doesn't load. i want to error check off of that.

my pseudo code is:

if dsn conenction is not present***
call sub to create connection
else
execute regular form load code
end if

im having trouble with the if condition line (*****).
any other suggestions?

Steve

everyday something new is learned, hmmm...

 

[Swi]

I would think the easiest way would be to try connecting and then trap the error with an error handling routine if it does not exist.

Swi

 

[sramey00]

u saying using on error next?

Steve

everyday something new is learned, hmmm...

 

[Swi]

Sub YourRoutine()

On Error GoTo errhandler
'YOUR DATABASE CONNECTION CODE


Exit Sub
errhandler:
Msgbox "DSN does not exist!", vbCritical
End Sub

Swi

 

[bjd4jc]

You could use code from the above FAQs to check to see if the DSN exists on the machine in your form load. I have adapted the code from faq222-82 to do just that:

Option Explicit

Private Const ERROR_SUCCESS = 0&
Private Const HKEY_LOCAL_MACHINE = &H80000002

Private Declare Function RegCloseKey Lib "advapi32" (ByVal hKey As Long) As Long

Private Declare Function RegOpenKey Lib "advapi32.dll" Alias "RegOpenKeyA" (ByVal hKey As Long, ByVal _
   lpSubKey As String, phkResult As Long) As Long

Private Function DSNExists(DSN As String) As Boolean
   Const ODBCINIPath = "Software\ODBC\ODBC.INI\"
   
   Dim TempPath As String
   Dim hCurKey As Long
   Dim lRegResult As Long
   
   TempPath = ODBCINIPath & DSN
   
   lRegResult = RegOpenKey(HKEY_LOCAL_MACHINE, TempPath, hCurKey)
   
    If lRegResult = ERROR_SUCCESS Then
        'it exists on the system
        DSNExists = True
        lRegResult = RegCloseKey(hCurKey)
    Else
        DSNExists = False
    End If
End Function

Private Sub Form_Load()
    If DSNExists("YOUR_DSN_NAME_HERE") Then
        'Code here to connect
    Else
        'Give user option to create
    End If
End Sub

 

[sramey00]

the registry... im sittin here thinking how to check if the actual connection works, but looking for it in the registry is the easiest way.

Much thanks!

Steve

everyday something new is learned, hmmm...

 

[sramey00]

is there a way to code a password while creating the dsn through vb? i tried including a password registry create key, but when i looked in the registry at the key i made versus the key that the ODBC admin tool creates, it was different. the ODBC admin tool doesn't list the password in the registry.
or does it, but its hidden?
is there a faq on this?
msdn didn't ahve anythin i could find, just how to create a regular log in session.

Steve

everyday something new is learned, hmmm...

 

[bjd4jc]

It depends on the ODBC datasource. What kind of database are you connecting to.

 

[sramey00]

sql server 2000

Steve

everyday something new is learned, hmmm...

 

[bjd4jc]

I do not know of a way to speicify a password for SQL Server 2000. Maybe someone else here does??

I always just specify the user and password when I connect to the database.

My other question is why are you going through all the trouble of having a DSN when there is an OLEDB provider for SQL Server? You can use a connection string like this and not have to worry about ODBC (assuming your using ADO):

Dim con As ADODB.Connection

Set Con = New ADODB.Connection

con.ConnectionString = "Provider=sqloledb.1;User ID=YOUR_USER;Password=YOUR_PASSWORD;Data Source=SERVER_NAME;Initial Catalog=DATABASE_NAME"

con.Open

....

 

[sramey00]

just to simplify installation and upgrading of the program. our network services is a little edgy about apps that are installed on the office computers. for long term use, if we just code a DSN then we dont have to change the code if we change the server name, then re-issue the app to our salesreps with the network services permission. we then just have to change the the reference the dsn connects to.

believe me, if i could use an ADO connection, id prefer it. i actually had to change the ADO conn to ODBC conn..



Steve

everyday something new is learned, hmmm...