[RESOLVED] Dynamically Obtaining ODBC Data Source Name

[NateBrei]

I have a database application that works great on my machine. It uses an ODBC Sytem DSN to connect to an AS/400 system. My system uses a specific name (let's call it 'AS400-01') to access the data. My application will be deployed to others in my organization all of whom will have access to the same AS/400 system. However, their System DSN may (most likely) be named differently (ie. 'MyAS400').

Here is my connection statements for this connection.

Set db = New Connection
    db.CursorLocation = adUseClient
    db.Open "PROVIDER=MSDASQL;dsn=[b]AS400-01[/b];uid=" & strUserID & ";pwd=" & _
        strPassword & ";"

Is there anyway I can dynamically get the user's dsn based on the AS400 system name to which the dsn points? (I hope this makes some sense.....)

Thanks in advance for any help.
Nate

[zemp]

If you make the DSN a variable then it can be done.

First, you will need a way that the user can select or enter the correct DSN on their local machine.

Second, you will need a place to store and reuse this DSN. That way the user will only need to be asked once. I like to store it in the registry.

Your connection code would be as follows:

Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDASQL;dsn=" & strMydsn & ";uid=" & strUserID & ";pwd=" & _
strPassword & ";"

To allow the user to select a dsn from all available dsn names on their machine use the following code in a module to populate a list box with the DSN names.

Option Explicit
Option Compare Text

Private Declare Function SQLDataSources Lib "ODBC32.DLL" (ByVal henv As Long, ByVal fDirection As Integer, ByVal szDSN As String, ByVal cbDSNMax As Integer, pcbDSN As Integer, ByVal szDescription As String, ByVal cbDescriptionMax As Integer, pcbDescription As Integer) As Integer
Private Declare Function SQLAllocEnv Lib "ODBC32.DLL" (env As Long) As Integer

Public Function DSNList(DB_Driver_Name As String)
Dim dSource As Integer
Dim strDSN As String * 1024
Dim strDRV As String * 1024
Dim szDSN As Integer
Dim szDRV As Integer
Dim hdlENV As Long
On Error Resume Next
If SQLAllocEnv(hdlENV) <> -1 Then
Do Until dSource <> 0
dSource = SQLDataSources(hdlENV, 1, strDSN, 1024, szDSN, strDRV, 1024, szDRV)
If Left$(strDRV, szDRV) = DB_Driver_Name Then
List1.AddItem Left$(strDSN, szDSN)
End If
Loop
End If
End Function

I am sure that you can handle the selection, storage and retrieval code on your own.

[NateBrei]

Thanks for the response, Zemp. I knew there had to be a way & I assumed that it would involve API (which I am very new, ignorant, & apprehensive about using). I will try adding your code to get the DSN's to my project first thing tomorrow morning. I'll let you know how it works.

Nate

[NateBrei]

Zemp, thanks a bunch for the code. It works great!! However, I think you give me too much credit.

I like to store it in the registry. I am sure that you can handle the ... storage and retrieval code on your own.

First, can you tell me where I can find out more information on how the (API???) functions you used work? I've looked at the API Text viewer that comes with VB & searched the help with no success.

Second, I've never attempted (& don't have a clue how) to write to or read from (or do anything else for that matter with) the Registry. Unless you or someone else can provide some easy to use code, I'm inclined to just write a 1 line text file to the application directory & read that each time the program runs.

Any additional help will be appreciated. Thanks again.
Nate

[zemp]

Reading from and writing to the registry may seem daunting but it is really very simple. That is because windows has some reserved registry keys for us to use

[HKEY_CURRENT_USER\Software\VB and VBA Program Settings]

and two built in functions that can access them easily, 'GetSetting' and 'SaveSetting'. NO api calls are required.

I'll try and walk you through it. I use three basic steps.

1. Create the necessary registration key.
2. Get the value from the registry.
3. save the value from the registry.

Now the details.

1. Open notepad and type the following;

REGEDIT4

[HKEY_CURRENT_USER\Software\VB and VBA Program Settings\AppName\ODBC]
"Name"=""

Save the file as ?.reg. This creates a file that the package and deployment wizard can use to add the requested registry keys when you install the program. You have to add it manually. You can also double-click on this file and the keys will be added to your computer. Use Start-Run-regedit to view the registry.

2. To retrieve the information into a variable that you can use the getsetting function like this;

strODBC = GetSetting("AppName", "ODBC", "Name", "")

3. To save the odbc name to the registry use the savesetting function like this;

SaveSetting "AppName", "ODBC", "Name", strODBC

That's all the code you need.

Note the connection between 'AppName', 'ODBC', and 'Name'. These names can be whatever you want, but they must match in order for the functions to find the information.

For more information on the two functions just look them up in the help files or on the msdn web site.

[NateBrei]

Zemp, thanks for all your excellent advice. I have tried ALMOST everything you've provided, but I'm still a little hesitant about writing to the Registry. Besides, I have to get approval from our system support people to create an application that affects the registry. But, this brings up a couple of questions.

1) In your earlier post, you mentioned creating a registry key entry in Notepad & saving it as ?.reg. Then I need to manually add that file to my P & D wizard. Notepad wouldn't let me save the file with the name of ?.reg. My question regarding this is: Does the question mark have special significance or does it mean to substitute some (more) descriptive name? And, will the P & D Wizard autoexecute the file to enter the Registry key no matter what the name as long as it has the .reg extension?

2) You said I could double-click this file to execute it in order to put that key into my Registry (or I could do a Start, Run, RegEdit). I attempted the RegEdit method, but I get a message that Registry editing has been disabled by the administrator. Would that also preclude me from double-clicking the file and/or getting the key entered if I run the P & D wizard?

3) I've been informed that, for the ODBC driver that we use to connect to our AS400, once the DSN is opened it remains open until the system is rebooted or the person logs out of the network. That means that I don't have to pass the Connection object the UserID & Password in my connection string unless the DSN has not been activated yet today. In those SQL API functions, is there a way to determine if a particular DSN is open or active (i.e. like with the .State on a Recordset object)?

Thanks again for your help. You've firmly put this project into the realm of possible.

Nate

[NateBrei]

Regarding the 3rd question, the reason I ask is because I don't have a way of getting the user's password to the AS400 unless I request it. However, I did test the ODBC connection and it does seem to remain open. For instance, if I open the DSN directly using our Emulator & then run my program without passing the UID & Password, everything works fine. However, if I reboot & then run my program immediately (without opening the DSN first) and don't pass the UID & password, my program bombs. In that case I need to pass the UID & password to get it to connect.

But, I don't want to always have to request the password from the user if they start & stop my program 2 - ???? times a day. So, I thought I could use an If on the state of the DSNName & pass different connection string parameters to the connection.open. Perhaps the following pseudocode would help.

'Test if the ODBC DSN is open/active [b]SOMEHOW[/b]
    If strDSNName.State = adOpen then
        'Use connection strings without UID & Password
        db.Open "PROVIDER=MSDASQL;dsn=" & strDSNName & ";"
        adodcInvMast.ConnectionString = "Provider=MSDASQL.1;" & _
            "Persist Security Info=True;Data Source=" & strDSNName & ";" & _
            "Initial Catalog=MyData;"
    Else
        'Display form to get password
        frmLogon.Show
        'Use connection strings with UID & Password
        db.Open "PROVIDER=MSDASQL;dsn=" & strDSNName & ";uid=" & strUserID & _
            ";Password=" & strPassword
        adodcInvMast.ConnectionString = "Provider=MSDASQL.1;" & _
            "Persist Security Info=True;Data Source=" & strDSNName & ";" & _
            "Initial Catalog=MyData;User ID=" & strUserID & _
            ";Password=" & strPassword
    End If

Hope this helps to explain why I asked the question.
Nate

[zemp]

I guess that it was the password and uid that through me. When I make a connection through a DSN the code to open the connection is very simple.

Connection.open "DSN=mydsn"

That's it. The ODBC DSN takes care of the passwords and ids. So I don't have to. BUT I have never done it within the environment that you are using so I can't comment on whether how you are doing it is best or correct, etc., etc..

Bottom line: Use what works.

Thanks for taking the time to clarify that for me.

[NateBrei]

Thanks, Zemp for all your help. I couldn't have done anything like this without your help.

A few things I noticed.
1) I attempted to create the .reg file as you suggested, but the P & D wizard didn't recognize it as a valid registry file. So, I ended up having to close the P & D and starting it over to leave it out (as a test). I had someone else load it on their machine (obviously without the .reg file), and the application worked fine. Evidently, SetSetting will write to the registry even if the Key entry hasn't been pre-defined.

2) The way I wrote the program, it tries to read the ODBC DSN name from the registry & if it returns a blank, I display a form with a list box where I use your code to get specific DSN type names. The user selects one & I write it to the registry & close the form and then use that name in the connection string. The user then never sees that DSN select form again since the value is in the registry. WAY COOL!!!

3) Also, the DSN connection once it is opened remains open until the machine is shut down or rebooted. When it is open, my application can issue the connection string on the DSN without the userid or password. However, if it hasn't been opened yet (i.e. my application is the first thing they use to connect to the data), trying to connect without the userid & password generates an error. So, I ended up trapping for that error & when it occurs I present the user with a userid/password form & then make the connection with those values. This too is WAY COOL because I only have to prompt for the password if the connection hasn't been opened yet for this session. They don't have to be prompted for it each time.

Once again, thanks so much for your help. I hope someday I may be able to return the favor.
Nate

[NateBrei]

Thanks, Zemp for all your help. I couldn't have done anything like this without your help.

A few things I noticed.
1) I attempted to create the .reg file as you suggested, but the P & D wizard didn't recognize it as a valid registry file. So, I ended up having to close the P & D and starting it over to leave it out (as a test). I had someone else load it on their machine (obviously without the .reg file), and the application worked fine. Evidently, SetSetting will write to the registry even if the Key entry hasn't been pre-defined.

2) The way I wrote the program, it tries to read the ODBC DSN name from the registry & if it returns a blank, I display a form with a list box where I use your code to get specific DSN type names. The user selects one & I write it to the registry & close the form and then use that name in the connection string. The user then never sees that DSN select form again since the value is in the registry. WAY COOL!!!

3) Also, the DSN connection once it is opened remains open until the machine is shut down or rebooted. When it is open, my application can issue the connection string on the DSN without the userid or password. However, if it hasn't been opened yet (i.e. my application is the first thing they use to connect to the data), trying to connect without the userid & password generates an error. So, I ended up trapping for that error & when it occurs I present the user with a userid/password form & then make the connection with those values. This too is WAY COOL because I only have to prompt for the password if the connection hasn't been opened yet for this session. They don't have to be prompted for it each time.

Once again, thanks so much for your help. I hope someday I may be able to return the favor.
Nate

[zemp]

Your Welcome!