modify the path of a system data source (microsoft text driver) data source from vb

need code that can modify an existing system data source (microsoft text driver) and change the file path to c:\mydata
Who is Participating?
mitaiAuthor Commented:
Here is code I found to add a dsn (user but should work for others as well)  the problem is that when it adds the Microsoft text driver dsn it sets use current directory flag instead of taking the path I pass to it.  what do I need to change to get this to work

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 Declare Function RegCloseKey Lib "advapi32" (ByVal hKey As Long) As Long
Private Declare Function RegOpenKeyEx Lib "advapi32" Alias "RegOpenKeyExA" _
    (ByVal hKey As Long, ByVal lpSubKey As String, ByVal ulOptions As Long, _
    ByVal samDesired As Long, ByRef phkResult As Long) As Long
Private Declare Function RegQueryValueEx Lib "advapi32" Alias _
    "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
    ByVal lpReserved As Long, ByRef lpType As Long, ByVal lpData As String, _
    ByRef lpcbData As Long) As Long

Const REG_SZ = 1
Const KEY_ALL_ACCESS = &H2003F
Const HKEY_CURRENT_USER = &H80000001
Const HKEY_LOCAL_MACHINE = &H80000002
Const ODBC_ADD_DSN = 1      ' Add data source
Const odbcremove = 3   ' Delete data source

Sub MakeDSN(ByVal sDSN As String, ByVal sDriver As String, _
    ByVal sDBFile As String, ByVal lAction As Long)

    Dim sAttributes As String
    Dim sDBQ As String
    Dim lngRet As Long
    Dim hKey As Long
    Dim regValue As String
    Dim valueType As Long
    ' query the Registry to check whether the DSN is already installed
    ' open the key
    If RegOpenKeyEx(HKEY_CURRENT_USER, "Software\ODBC\ODBC.INI\" & sDSN, 0, _
        KEY_ALL_ACCESS, hKey) = 0 Then
        ' zero means no error => Retrieve value of "DBQ" key
        regValue = String$(1024, 0)
               ' Allocate Variable Space
        If RegQueryValueEx(hKey, "DBQ", 0, valueType, regValue, _
            Len(regValue)) = 0 Then
            ' zero means OK, so we can retrieve the value
            If valueType = REG_SZ Then
                sDBQ = Left$(regValue, InStr(regValue, vbNullChar) - 1)
            End If
        End If
        ' close the key
        RegCloseKey hKey
    End If
    ' Perform the action only if we're adding a DSN that doesn't exist
    ' or removing and existing DSN
    If (sDBQ = "" And lAction = ODBC_ADD_DSN) Or (sDBQ <> "" And lAction = odbcremove) Then
        ' check that the file actually exists
        If Len(sDBFile) = 0 Then
            MsgBox "Database file doesn't exist!", vbOKOnly + vbCritical
            Exit Sub
        End If
        sAttributes = "DSN=" & sDSN & vbNullChar & "DBQ=" & sDBFile & vbNullChar
        lngRet = SQLConfigDataSource(0&, lAction, sDriver, sAttributes)
    End If
End Sub

Private Sub Command2_Click()
sDriver = "Microsoft Text Driver (*.txt; *.csv)"
sName = "simplytest"
sFile = "C:\Users\ian stringer\Documents\Simply Accounting\forms\newtest\"
MakeDSN sName, sDriver, sFile, ODBC_ADD_DSN

End Sub
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.