I was wrestling with an interface to a CIM program over the holidays and came up with an interesting solution that I thought I would pass on for whatever it's worth.

The program I was trying to interface to VFP used Paradox as the database engine and created a separate directory for each job file it created. I needed to access those directories (literally hundreds) based on a part number entered by the user. What I needed was to be able to create DSN's on the fly. A search of the KB turned up article Q142216, Creating ODBC Data Sources using SqlConfigDataSource. I implemented the information in the article as follows:

LPARAMETER tcJobFile

DECLARE Integer SQLConfigDataSource in odbccp32.dll Integer, ;
Integer, String, String

settings="DSN=Job_Files"+chr(0)+;
"Description=Paradox ODBC Driver"+chr(0)+;
"DefaultDir=&tcJobFile"+chr(0)+;
"FIL=Paradox 3.x"+chr(0)+;
"CollatingSequence=ASCII"

tcNewDSN = SQLConfigDataSource(0,1,"Microsoft Paradox Driver (*.db )",settings)
RETURN tcNewDSN

I pass the directory path in tcJobFile and check for a confirmation in tcNewDSN. Works like a champ! As stated in the KB article, you may have to create a manual DSN for the ODBC driver you are using and go to the registry to see what entries are required. Word of caution: the driver name (next to the last line of code in my example) MUST be exactly as shown in the ODBC driver manager. I did not notice the space between *.db and the close parenthesis and typed it as *.db), which caused the function to fail. Took me a while to find the problem.

The function does not check to see if the DSN currently exists (which is a good thing) so I can use a single DSN in my code and just overwrite it each time the directory changes. Hope this can be useful to someone else.