>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.
I, too, did some searching around a few months ago on how to do this. If you ever need help with VFP and Oracle, I have a FAQ article in KB under Client/Server called "Connecting VFP to Oracle". Uses same API call, etc.
Mark McCasland
Midlothian, TX USA