Solved

ODBC DSN from visual Basic Code ?

Posted on 2002-05-02
8
680 Views
Last Modified: 2013-11-25
Hello,

  I want to create ODBC DSN through visual Basic Code at  
  run time.
  I want ODBC DSN through visual Basic Code for MS -
  Access and Oracle database.


  Thanks in advance.

  Regards
  dAshish
0
Comment
Question by:dashish
8 Comments
 

Expert Comment

by:VBMeera
Comment Utility
hey,
u can't create ODBC thr Vb (i haven't headr @ it)
puple can create odbc DSN using Administrative Tools and same DSN name can pass in string using adodb thru vb at runtime.
0
 

Expert Comment

by:chengxi
Comment Utility
i don't think you can creat the DSN connection using vb code...wat you can do is you need to create the DSN connection in the ODBC, then only you can connect to the database using vb code on run time...
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
pretty easy! I do it really often. See this article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;q184608
0
 
LVL 15

Accepted Solution

by:
cquinn earned 50 total points
Comment Utility
A Microsoft  Class module to do just this:  (From NeatCD - a Microsoft Access database)

Option Explicit

'**********************************************************************
'
' For an example that demonstrates the use of this class, please
' see the buildFileDSN procedure in the Call Neat Classes module.
'
' This class creates a File DSN ODBC datasource. It is currently
' designed for Microsoft SQL Server datasources only. The class has
' eight properties and one method to write the file. The driverName,
' serverName, and fileName properties are required. All others are
' optional. The optional properties can be set to an empty string
' as they are in the example, or they can be ommitted altogether. The
' default property value for the folder property is set by the class
' to the folder where File DSN files are normally stored. The class
' determines this location by querying the system registry. It is
' recommended that this not be changed by specifying an alternate
' folder unless you have a specific purpose for doing so. Additionally,
' the class uses an API call to determine the Workstation ID, so this
' function must be run on the machine where the FileDsn is to be used.
'
'**********************************************************************


Private Declare Function GetComputerName Lib "kernel32" _
    Alias "GetComputerNameA" (ByVal lpbuffer As String, nsize As Long) As Long
Private Declare Function RegQueryValueExNULL Lib "advapi32.dll" Alias "RegQueryValueExA" _
    (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, lpType As Long, ByVal lpData As Long, lpcbData As Long) As Long
Private Declare Function RegQueryValueExString Lib "advapi32.dll" Alias "RegQueryValueExA" _
    (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, lpType As Long, ByVal lpData As String, lpcbData As Long) As Long
Private Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA" _
    (ByVal hKey As Long, ByVal lpSubKey As String, ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As Long) As Long
Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long

Const HKEY_LOCAL_MACHINE = &H80000002
Const ERROR_NONE = 0
Const KEY_ALL_ACCESS = &H3F

Private driver As String, uid As String, pwd As String, apath As String
Private app As String, server As String, fName As String, datBase As String

Private Sub Class_Initialize()  ' Initialize properties to 0 length string.
    driver = ""
    uid = ""
    pwd = ""
    app = ""
    server = ""
    datBase = ""
    apath = ""
End Sub

Property Let dataBaseName(argDataBaseName As String)
    datBase = argDataBaseName
End Property

Property Let drivername(argDriverName As String)
    driver = argDriverName
End Property

Property Let user(argUid As String)
    uid = argUid
End Property

Property Let password(argPwd As String)
    pwd = argPwd
End Property

Property Let clientApp(argClientApp As String)
    app = argClientApp
End Property

Property Let serverName(argServerName As String)
    server = argServerName
End Property

Property Let filename(argFilename As String)
    fName = argFilename
End Property

Public Property Let folder(ByVal vNewValue As Variant)
    apath = vNewValue
End Property
Sub writeFile()
    Dim theFile As Integer
    Dim filename As String
    Dim Default_Path
    Default_Path = getDSNDefault()
    If apath <> "" Then
        filename = apath & "\" & fName & ".dsn"
    ElseIf IsEmpty(Default_Path) Then
        filename = "C:\" & fName & ".dsn"
        MsgBox "Your file was written as: " & filename, vbInformation, "Default ODBC Directory Not Found"
    Else
        filename = Default_Path & "\" & fName & ".dsn"
    End If
    theFile = FreeFile
    On Error GoTo WriteFile_Err
    Open filename For Output As #theFile
        Print #theFile, "[ODBC]"
        Print #theFile, "DRIVER=" & driver
        If Len(uid) > 0 Then
            Print #theFile, "UID=" & uid
        End If
        If Len(pwd) > 0 Then
            Print #theFile, "PWD=" & pwd
        End If
        Print #theFile, "DATABASE=" & datBase
        Print #theFile, "WSID=" & getmachine()
        Print #theFile, "APP=" & app
        Print #theFile, "SERVER=" & server
        Close #theFile
    Close #theFile

WriteFile_Err:
If Err.Number = 76 Then
    MsgBox "Directory does not exist." & vbCrLf & "File was written to: C:\", vbInformation, "Invalid Path Specified"
    filename = "c:\" & fName & ".dsn"
    Resume
End If
   
End Sub

Private Function getmachine()   ' Use API to get machine name (NT or WIN95).

    Dim length As Long
    Dim compname As String
    Dim returned
   
    length = 255
    compname = String$(255, 0)
    returned = GetComputerName(compname, length)
   
    If returned Then
        getmachine = left(compname, length)
    Else
        MsgBox "An error occured while retrieving the workstation id."
    End If

End Function
Private Function getDSNDefault() As Variant
       Dim lRetVal As Long         'result of the API functions
       Dim hKey As Long         'handle of opened key
       Dim vValue As Variant      'setting of queried value
       Dim sKeyName As String
       Dim sValueName As String
       
       sKeyName = "software\odbc\odbc.ini\odbc file dsn"
       sValueName = "DefaultDSNDir"
       lRetVal = RegOpenKeyEx(HKEY_LOCAL_MACHINE, sKeyName, 0, KEY_ALL_ACCESS, hKey)
       lRetVal = QueryValueEx(hKey, sValueName, vValue)
       If lRetVal = ERROR_NONE Then
            getDSNDefault = left(vValue, Len(vValue) - 1)
        Else
            getDSNDefault = Empty
       RegCloseKey (hKey)
       End If
   End Function

Private Function QueryValueEx(ByVal lhKey As Long, ByVal szValueName As String, vValue As Variant) As Long
    Dim cch As Long
    Dim lrc As Long
    Dim lType As Long
    Dim lValue As Long
    Dim sValue As String

    On Error GoTo QueryValueExError
   
    lrc = RegQueryValueExNULL(lhKey, szValueName, 0&, lType, 0&, cch)
    If lrc <> ERROR_NONE Then Error 5
    sValue = String(cch, 0)
    lrc = RegQueryValueExString(lhKey, szValueName, 0&, lType, sValue, cch)
            If lrc = ERROR_NONE Then
                vValue = left$(sValue, cch)
            Else
                vValue = Empty
            End If
           
QueryValueExExit:
    QueryValueEx = lrc
    Exit Function

QueryValueExError:
    Resume QueryValueExExit
End Function
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 5

Expert Comment

by:rkot2000
Comment Utility
you can use DAO.DBEngine.RegisterDatabase method to create a DSN, but I like uld files better.

0
 
LVL 1

Expert Comment

by:Moondancer
Comment Utility
ADMINISTRATION WILL BE CONTACTING YOU SHORTLY.  Moderators Computer101, Netminder or Mindphaser will return to finalize these if they are still open in 7 days.  Experts, please post closing recommendations before that time.

Below are your open questions as of today.  Questions which have been inactive for 21 days or longer are considered to be abandoned and for those, your options are:
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the participants why you wish to do this, and allow for Expert response.  This choice will include a refund to you, and will move this question to our PAQ (Previously Asked Question) database.  If you found information outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
   --> Post comments for expert of your intention to delete and why
   --> YOU CANNOT DELETE A QUESTION with comments; special handling by a Moderator is required.

For special handling needs, please post a zero point question in the link below and include the URL (question QID/link) that it regards with details.
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
Please click this link for Help Desk, Guidelines/Member Agreement and the Question/Answer process.  http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

Click you Member Profile to view your question history and please keep them updated. If you are a KnowledgePro user, use the Power Search option to find them.  

Questions which are LOCKED with a Proposed Answer but do not help you, should be rejected with comments added.  When you grade the question less than an A, please comment as to why.  This helps all involved, as well as others who may access this item in the future.  PLEASE DO NOT AWARD POINTS TO ME.

To view your open questions, please click the following link(s) and keep them all current with updates.
http://www.experts-exchange.com/questions/Q.20157716.html
http://www.experts-exchange.com/questions/Q.20162024.html
http://www.experts-exchange.com/questions/Q.20189356.html
http://www.experts-exchange.com/questions/Q.20192119.html
http://www.experts-exchange.com/questions/Q.20296631.html



*****  E X P E R T S    P L E A S E  ******  Leave your closing recommendations.
If you are interested in the cleanup effort, please click this link
http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643
POINTS FOR EXPERTS awaiting comments are listed in the link below
http://www.experts-exchange.com/commspt/Q.20277028.html
 
Moderators will finalize this question if in @7 days Asker has not responded.  This will be moved to the PAQ (Previously Asked Questions) at zero points, deleted or awarded.
 
Thanks everyone.
Moondancer
Moderator @ Experts Exchange
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
points to me.
0
 

Author Comment

by:dashish
Comment Utility
Thanks for your valuable suggestions.
Regards
dAshish
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now