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
mitaiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
DoEvents

End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.