Modifying a file.dsn outside of the ODBC Control Panel

Posted on 2004-11-02
Last Modified: 2008-01-09
I have created a file DSN using the ODBC control panel. The file DSN appears under the 3rd tab inside the control panel. I have configured the file DSN to include the directory and format for the file.

I need to change the file DSN on the fly. I have gotten suggestions to use tools like Notepad or Wordpad which I have. The file opens up alright but once I modify the file in either Notepad or Wordpad the file becomes unreadable in the ODBC control panel with the following error:

“General Error: Invalid File DSN”

So I then reposted the question and I got the suggestion that if I opened the file via VB Data Environment I should be able to read and modify the file without a problem. Great. Can you see a way to modify a text in the Data Environment ? Because I define a new connection under the DE object and using the Microsoft OLE DB Provider for ODBC Drivers I create a link to the file VIA the file DSN that I originally created. I then add a command and select Table which in fact does show me the file. But once I try to drag that object onto a form I get the following error:

[Microsoft][ODBC Text Driver] Cannot Update DataBase or Object is read only.

Ok. So given that that avenue was closed I they tried to do the whole via the Filesystemobject in VB, as follows:

Sub ShowFileList()
   Const ForReading = 1, ForWriting = 2, ForAppending = 8
   Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
   Dim fso, tsIn, dsnIn, dsnOut
   'General Object
   Set fso = CreateObject("Scripting.FileSystemObject")
   'Read File
   Set dsnIn = fso.GetFile("C:\Program Files\Common Files\ODBC\Data Sources\WinpasTxtIn.dsn")
   Set tsIn = dsnIn.OpenAsTextStream(ForReading, TristateUseDefault)
   'Write File
   fso.CreateTextFile "C:\Program Files\Common Files\ODBC\Data Sources\WinpasTxtOut.dsn"
   Set dsnOut = fso.GetFile("C:\Program Files\Common Files\ODBC\Data Sources\WinpasTxtOut.dsn")
   Set tsOut = dsnOut.OpenAsTextStream(ForWriting, TristateUseDefault)
   Do While tsIn.AtEndOfStream <> True
      TextStream = tsIn.ReadLine
      tsOut.Write TextStream
End Sub

So what’s happening here is the input file – dsnin – is not impacted in any way and is still accessible via the ODBC Control Panel. But the output file dsnout is corrupted with the same error as above:

“General Error: Invalid File DSN”

So what is going on here ? I mean there has to be a way to write out file DSN files outside of the ODBC control panel without encountering errors and corrupted files.

Any ideas ?



Question by:ejr19592004
    1 Comment
    LVL 12

    Accepted Solution

    It's done via editing the registry using Win32 API function calls.  Here's the how-to directly from Microsoft:  ;en-us;171146
    Here's another:;en-us;184608

    I've used both and they seem to work as expected without incident, including being able to get to them via the "normal" ODBC configuration utility.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
    Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    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…

    729 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

    19 Experts available now in Live!

    Get 1:1 Help Now