API call to get List of ODBC DSNs

Vlip

New Member
Joined
Mar 10, 2002
Messages
49
Hi,

Does anyone have an API that will allow me to get a list of ODBC data sources defined in the Windows ODBC Control Panel?

Thanks in advance,
Vlip
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Much easier to call it out of the registry

HKEY_LOCAL_MACHINE
SOFTWARE
ODBC
ODBCINST.INI <<<< There's the list

Good Luck!
Tom
 
Upvote 0
Hi,

Thanks for your help! I'm sorry but I am new at this and am going to need a little more help. On my system,ODBCINST.INI lists all the ODBC drivers installed. I want User and System DSNs created with these drivers. There is an ODBC.INI key that lists the system DSNs but not the User DSNs. I don't see a key in the path specified that lists User DSNs. Also, I don't really know how to get this list out of the registry. Any help there would be greatly appreciated.

Vlip
 
Upvote 0
Sorry
Misunderstood you



This is where you will find user DSN's
HKEY_CURRENT_USERSoftwareOdbcOdbc.iniOdbc Data sources


This where you will find system DSN's

HKEY_LOCAL_MACHINESoftwareOdbcOdbc.iniOdbc Data sources

How do you need the data set up after taking it from the reistry?

Please Reply

Tom
 
Upvote 0
Hi,

Thanks for the reply. I have an app that allows users to query any one of a number of Oracle databases using the DAO library. I have a dialog box where the user now types in the Data source name as it appears in the Windows Control panel. They also have to type in all of the same information (user id, password, database name, etc) that is already entered in the control panel. I would like to change this from a simple text field to a listbox where the user can select the DSN and then I can read all the pertinent information into the appropriate fields. I have some code that allows me to read the value of a registry key that I obtained from John Walkenbach's book (Excel 2002 Power Programming with VBA) but I can't figure out how to obtain the actual list of keys in a specified path.
I suppose I would like to retrieve this list as an array of strings so I can set the .List property of the listbox.
Thanks again for all the help!

Br,
Vlip
 
Upvote 0
Hi Again.
Are you perhaps speaking of File DSN's?
If all you need are user DSN's & Drivers.
In Sys Path see odbc.ini
Will list User Dsn's

If your frontend is running on the same or similiar systems, why not create a simple text file with settings information for each connection?

All the user should have to do is choose Oracle.SomeData and type in username and pw.

When the user chooses, your list would be populated with whatever you choose...

Am I missing your point here?

I apoligize if my density level is reaching critical mass! Am trying to understand what your deal is???

Tom
 
Upvote 0
No need to apologize. I greatly appreciate your assistance. I will try to give a clearer explanation of what I am after.
If you open the ODBC control panel, there is a button labelled Add. Click this button and you get another dialog box where you choose the driver to use, give the data source a name and enter other settings. This data source definition will then appear under the User DSNs tab or the System DSNs tab if that was selected before creating the new instance. My users already have these DSNs set up, one for each database they need to connect to. Ocassionally, a new database is added or one may be dropped or have some aspect altered.
I have created an Excel application as an add-in that has the current information stored in a worksheet but because these databases may change as I just stated, I have created a dialog box that allows the end-user to edit the list of DSNs stored in the worksheet. Rather than have the user simply type in the name of the DSN to use as it appears in the control panel, I would like to have a drop-down list that they can select from since some users don't even know how to access it or even if they did they might mis-type it. By the way, these DSNs are updated automatically by a corporate log-on script that I have no control over.
All I want is an array of strings that contains the names of the data source definitions configured on the computer running my app. You have already given me the path to the User and System DSNs, now all I need is a little help figuring out how to get this list out of the registry. As I mentioned before, given the path and registry key name, I have code that will return the value (for example, the UserID, password, etc). What I need help with however, is in obtaining the list of entries at a path two levels higher in the registry. That is, I want the list of items at ODBC.INI as an array of strings. I hope this better describes what I am trying to do. Thanks again for all your help.

Best regards,
Vlip
 
Upvote 0
Hi
This will be the first of three replies:
Please add ############################# to the lines of data you are needing to extract from the registry.
If you would rather E-Mail than post the keys needed: TsTom@hotmail.com

See next two replies:

Tom
This message was edited by TsTom on 2002-04-02 09:19
 
Upvote 0
System DSN


REGEDIT4

[HKEY_LOCAL_MACHINESoftwareODBCODBCINST.INI]

[HKEY_LOCAL_MACHINESoftwareODBCODBCINST.INIODBC Core]
"UsageCount"=dword:00000004

[HKEY_LOCAL_MACHINESoftwareODBCODBCINST.INIMicrosoft Text Driver (*.txt; *.csv)]
"UsageCount"=dword:00000003
"Driver"="C:\WINDOWS\SYSTEM\odbcjt32.dll"
"Setup"="C:\WINDOWS\SYSTEM\odtext32.dll"
"APILevel"="1"
"ConnectFunctions"="YYN"
"DriverODBCVer"="02.50"
"DSN"="Text Files"
"FileExtns"="*.,*.asc,*.csv,*.tab,*.txt,*.csv"
"FileUsage"="1"
"SQLLevel"="0"
"DriverID"="27"

[HKEY_LOCAL_MACHINESoftwareODBCODBCINST.INIODBC Drivers]
"Microsoft Text Driver (*.txt; *.csv)"="Installed"
"Microsoft Access Driver (*.mdb)"="Installed"
"Microsoft dBase Driver (*.dbf)"="Installed"
"Microsoft Excel Driver (*.xls)"="Installed"
"Microsoft ODBC for Oracle"="Installed"
"Microsoft Paradox Driver (*.db )"="Installed"
"SQL Server"="Installed"
"Microsoft Visual FoxPro Driver"="Installed"
"Microsoft dBase VFP Driver (*.dbf)"="Installed"
"Microsoft FoxPro VFP Driver (*.dbf)"="Installed"
"Microsoft FoxPro Driver (*.dbf)"="Installed"

[HKEY_LOCAL_MACHINESoftwareODBCODBCINST.INIMicrosoft Access Driver (*.mdb)]
"UsageCount"=dword:00000003
"Driver"="C:\WINDOWS\SYSTEM\odbcjt32.dll"
"Setup"="C:\WINDOWS\SYSTEM\odbcjt32.dll"
"APILevel"="1"
"ConnectFunctions"="YYN"
"DriverODBCVer"="02.50"
"DSN"="MS Access 97 Database"
"FileExtns"="*.mdb"
"FileUsage"="2"
"SQLLevel"="0"
"UID"="Admin"

[HKEY_LOCAL_MACHINESoftwareODBCODBCINST.INIMicrosoft dBase Driver (*.dbf)]
"UsageCount"=dword:00000003
"Driver"="C:\WINDOWS\SYSTEM\odbcjt32.dll"
"Setup"="C:\WINDOWS\SYSTEM\oddbse32.dll"
"APILevel"="1"
"ConnectFunctions"="YYN"
"DriverODBCVer"="02.50"
"DSN"="dBASE Files"
"FileExtns"="*.dbf,*.ndx,*.mdx"
"FileUsage"="1"
"SQLLevel"="0"
"DriverId"="533"

[HKEY_LOCAL_MACHINESoftwareODBCODBCINST.INIMicrosoft Excel Driver (*.xls)]
"UsageCount"=dword:00000003
"Driver"="C:\WINDOWS\SYSTEM\odbcjt32.dll"
"Setup"="C:\WINDOWS\SYSTEM\odexl32.dll"
"APILevel"="1"
"ConnectFunctions"="YYN"
"DriverODBCVer"="02.50"
"DSN"="Excel Files"
"FileExtns"="*.xls"
"FileUsage"="1"
"SQLLevel"="0"
"DriverId"="22"

[HKEY_LOCAL_MACHINESoftwareODBCODBCINST.INIMicrosoft ODBC for Oracle]
"UsageCount"=dword:00000002
"Driver"="C:\WINDOWS\SYSTEM\msorcl32.dll"
"Setup"="C:\WINDOWS\SYSTEM\msorcl32.dll"
"APILevel"="2"
"ConnectFunctions"="YYY"
"DriverODBCVer"="02.50"
"FileUsage"="0"
"SQLLevel"="1"
"CPTimeout"="60"

[HKEY_LOCAL_MACHINESoftwareODBCODBCINST.INIMicrosoft Paradox Driver (*.db )]
"UsageCount"=dword:00000003
"Driver"="C:\WINDOWS\SYSTEM\odbcjt32.dll"
"Setup"="C:\WINDOWS\SYSTEM\odpdx32.dll"
"APILevel"="1"
"ConnectFunctions"="YYN"
"DriverODBCVer"="02.50"
"DSN"="Paradox Files"
"FileExtns"="*.db"
"FileUsage"="1"
"SQLLevel"="0"
"DriverId"="538"

[HKEY_LOCAL_MACHINESoftwareODBCODBCINST.INISQL Server]
"UsageCount"=dword:00000004
"Driver"="C:\WINDOWS\SYSTEM\SQLSRV32.dll"
"Setup"="C:\WINDOWS\SYSTEM\SQLSRV32.dll"
"APILevel"="2"
"ConnectFunctions"="YYY"
"DriverODBCVer"="02.50"
"FileUsage"="0"
"SQLLevel"="1"
"CPTimeout"="60"

[HKEY_LOCAL_MACHINESoftwareODBCODBCINST.INIMicrosoft Visual FoxPro Driver]
"UsageCount"=dword:00000002
"Driver"="C:\WINDOWS\SYSTEM\vfpodbc.dll"
"Setup"="C:\WINDOWS\SYSTEM\vfpodbc.dll"
"APILevel"="0"
"ConnectFunctions"="YYN"
"DriverODBCVer"="02.50"
"DSN"="Visual FoxPro Tables"
"FileExtns"="*.dbf,*.cdx,*.idx,*.fpt"
"FileUsage"="1"
"SQLLevel"="0"
"SourceDB"=""
"SourceType"="DBF"

[HKEY_LOCAL_MACHINESoftwareODBCODBCINST.INIMicrosoft dBase VFP Driver (*.dbf)]
"UsageCount"=dword:00000001
"Driver"="C:\WINDOWS\SYSTEM\VFPODBC.DLL"
"Setup"="C:\WINDOWS\SYSTEM\VFPODBC.DLL"
"APILevel"="0"
"ConnectFunctions"="YYN"
"DriverODBCVer"="02.50"
"DSN"="dBase Files - Word"
"FileExtns"="*.dbf,*.dbc"
"FileUsage"="1"
"SQLLevel"="0"
"CPTimeout"="<not pooled>"
"SourceDB"=""
"SourceType"="DBF"

[HKEY_LOCAL_MACHINESoftwareODBCODBCINST.INIMicrosoft FoxPro VFP Driver (*.dbf)]
"UsageCount"=dword:00000001
"Driver"="C:\WINDOWS\SYSTEM\VFPODBC.DLL"
"Setup"="C:\WINDOWS\SYSTEM\VFPODBC.DLL"
"APILevel"="0"
"ConnectFunctions"="YYN"
"DriverODBCVer"="02.50"
"DSN"="FoxPro Files - Word"
"FileExtns"="*.dbf,*.dbc"
"FileUsage"="1"
"SQLLevel"="0"
"CPTimeout"="<not pooled>"
"SourceDB"=""
"SourceType"="DBF"

[HKEY_LOCAL_MACHINESoftwareODBCODBCINST.INIMS Code Page Translator]
"Translator"="C:\WINDOWS\SYSTEM\MSCPXL32.dll"
"Setup"="C:\WINDOWS\SYSTEM\MSCPXL32.dll"
"UsageCount"=dword:00000003

[HKEY_LOCAL_MACHINESoftwareODBCODBCINST.INIODBC Translators]
"MS Code Page Translator"="Installed"

[HKEY_LOCAL_MACHINESoftwareODBCODBCINST.INIMicrosoft FoxPro Driver (*.dbf)]
"UsageCount"=dword:00000001
"Driver"="C:\WINDOWS\SYSTEM\odbcjt32.dll"
"Setup"="C:\WINDOWS\SYSTEM\odfox32.dll"
"APILevel"="1"
"ConnectFunctions"="YYN"
"DriverODBCVer"="02.50"
"FileUsage"="1"
"FileExtns"="*.dbf,*.cdx,*.idx,*.ftp"
"SQLLevel"="0"
 
Upvote 0
User DSN's

REGEDIT4

[HKEY_CURRENT_USERSoftwareODBCODBC.INI]

[HKEY_CURRENT_USERSoftwareODBCODBC.INIODBC Data Sources]
"MS Access Database"="Microsoft Access Driver (*.mdb)"
"dBASE Files"="Microsoft dBase Driver (*.dbf)"
"Excel Files"="Microsoft Excel Driver (*.xls)"
"Visual FoxPro Database"="Microsoft Visual FoxPro Driver"
"Visual FoxPro Tables"="Microsoft Visual FoxPro Driver"
"dBase Files - Word"="Microsoft dBase VFP Driver (*.dbf)"
"FoxPro Files - Word"="Microsoft FoxPro VFP Driver (*.dbf)"

[HKEY_CURRENT_USERSoftwareODBCODBC.INIMS Access Database]
"Driver"="C:\WINDOWS\SYSTEM\ODBCJT32.DLL"
"DriverId"=dword:00000019
"SafeTransactions"=dword:00000000
"UID"=""

[HKEY_CURRENT_USERSoftwareODBCODBC.INIMS Access DatabaseEngines]

[HKEY_CURRENT_USERSoftwareODBCODBC.INIMS Access DatabaseEnginesJet]
"ImplicitCommitSync"=""
"Threads"=dword:00000003
"UserCommitSync"="Yes"

[HKEY_CURRENT_USERSoftwareODBCODBC.INIdBASE Files]
"Driver"="C:\WINDOWS\SYSTEM\ODBCJT32.DLL"
"DriverId"=dword:00000215
"SafeTransactions"=dword:00000000
"UID"=""

[HKEY_CURRENT_USERSoftwareODBCODBC.INIdBASE FilesEngines]

[HKEY_CURRENT_USERSoftwareODBCODBC.INIdBASE FilesEnginesXbase]
"ImplicitCommitSync"=""
"Threads"=dword:00000003
"UserCommitSync"="Yes"

[HKEY_CURRENT_USERSoftwareODBCODBC.INIExcel Files]
"Driver"="C:\WINDOWS\SYSTEM\ODBCJT32.DLL"
"DriverId"=dword:00000316
"SafeTransactions"=dword:00000000
"UID"=""

[HKEY_CURRENT_USERSoftwareODBCODBC.INIExcel FilesEngines]

[HKEY_CURRENT_USERSoftwareODBCODBC.INIExcel FilesEnginesExcel]
"ImplicitCommitSync"=""
"Threads"=dword:00000003
"UserCommitSync"="Yes"
"FirstRowHasNames"=hex:01

[HKEY_CURRENT_USERSoftwareODBCODBC.INIVisual FoxPro Database]
"Driver"="C:\WINDOWS\SYSTEM\VFPODBC.DLL"
"SourceDB"=""
"Description"=""
"SourceType"="DBF"
"BackgroundFetch"="Yes"
"Exclusive"="No"
"Null"="Yes"
"Deleted"="Yes"
"Collate"="MACHINE"
"SetNoCountOn"="No"

[HKEY_CURRENT_USERSoftwareODBCODBC.INIVisual FoxPro Tables]
"Driver"="C:\WINDOWS\SYSTEM\VFPODBC.DLL"
"SourceDB"=""
"Description"=""
"SourceType"="DBF"
"BackgroundFetch"="Yes"
"Exclusive"="No"
"Null"="Yes"
"Deleted"="Yes"
"Collate"="MACHINE"
"SetNoCountOn"="No"

[HKEY_CURRENT_USERSoftwareODBCODBC.INIdBase Files - Word]
"Driver"="C:\WINDOWS\SYSTEM\VFPODBC.DLL"
"SourceDB"=""
"Description"=""
"SourceType"="DBF"
"BackgroundFetch"="Yes"
"Exclusive"="No"
"Null"="Yes"
"Deleted"="Yes"
"Collate"="MACHINE"
"SetNoCountOn"="No"

[HKEY_CURRENT_USERSoftwareODBCODBC.INIFoxPro Files - Word]
"Driver"="C:\WINDOWS\SYSTEM\VFPODBC.DLL"
"SourceDB"=""
"Description"=""
"SourceType"="DBF"
"BackgroundFetch"="Yes"
"Exclusive"="No"
"Null"="Yes"
"Deleted"="Yes"
"Collate"="MACHINE"
"SetNoCountOn"="No"
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top