How to Create a DSN from VB...

[lvramanan]

Hi Guys,
I need to know is there any way to create a DSN for a Database (For Example Oracle or Access) using API. Is there any API for doing this. Otherwise any other method should be used. Additionally for Access we need to specify the Database Name. But for Oracle it is not needed. How should we check for the Existence of Oracle on a Machine ? First of all Is it Possible. Thnx in advance for any help.

[crispin]

Q171146

[Hack]

Here is some code that will either create, or delete, a DSN for Oracle:

Private Const ODBC_ADD_DSN = 1 ' Add data source
Private Const ODBC_CONFIG_DSN = 2 ' Configure (edit) data source
Private Const ODBC_REMOVE_DSN = 3 ' Remove data source
Private Const vbAPINull As Long = 0& ' NULL Pointer

Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal hwndParent As Long, ByVal fRequest As Long, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long

Private Sub CreateDSN(sDSN As String)
Dim nRet As Long
Dim sDriver As String
Dim sAttributes As String

sDriver = "Oracle73 Ver 2.5"
sAttributes = "Server=pressdb.world" & Chr$(0)
sAttributes = sAttributes & "DESCRIPTION=" & sDSN & Chr$(0)
sAttributes = sAttributes & "DSN=" & sDSN & Chr$(0)
sAttributes = sAttributes & "DATABASE=DB" & Chr$(0)
sAttributes = sAttributes & "UID=Waty" & Chr$(0)
sAttributes = sAttributes & "PWD=myPassword" & Chr$(0)

DBEngine.RegisterDatabase "kiki", "Oracle73 Ver 2.5", True, sAttributes

nRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, sDriver, sAttributes)
End Sub

Private Sub DeleteDSN(sDSN As String)
Dim nRet As Long
Dim sDriver As String
Dim sAttributes As String

sDriver = "Oracle73 Ver 2.5"
sAttributes = sAttributes & "DSN=" & sDSN & Chr$(0)

nRet = SQLConfigDataSource(vbAPINull, ODBC_REMOVE_DSN, sDriver, sAttributes)

End Sub

[yrwyddfa]

Perhaps you should look at connecting using a DSN-Less connection, and storing the configuration information in the registry.

You get to use OLE-DB then which with ADO has far superior performance.

[lvramanan]

Hi yrwyddfa,
Can u give me a example on how should I use a OLE-DB Connection. Thnx in Advance.

[yrwyddfa]

I can't test this, because I haven't got Oracle, but I guess it would be something like this:

Function GetCn( _
                ByVal sServer As String, _
                ByVal sDatabase As String, _
                ByVal sUID As String, _
                ByVal sPassword As String _
                ) _
                As ADODB.Connection
                    
                    
    Dim oCn As ADODB.Connection
    Dim sCn As String
    
    Set oCn = CreateObject("ADODB.Connection")
    
    oCn.Provider = "MSDAORA"
    oCn.Properties("User ID") = "Waty"
    oCn.Properties("Data Source") = "pressdb.world"
    oCn.Properties("Initial Catalog") = "DB"
    
    oCn.Open
    
    If oCn.State <> adStateOpen Then
        'raise errors here
        Set GetCn = Nothing
    Else
        Set GetCn = oCn
    End If
    
End Function

I hope this helps a little.

[lvramanan]

Hi yrwyddfa,
I got ur code and mail u after I got it working. Thnx for ur help.
Regards,
Ramanan.