Solved

Create a system ODBC data source

Posted on 2000-03-10
14
505 Views
Last Modified: 2013-12-25
Does anyone know how I can programatically create a System ODBC Data source from VB without directly writing to the registry?  I have looked at the rdoRegisterDataSource RDO function and the SQLConfigDataSource ODBC API call but both of those seem to create User data sources.

My application currently writes directly to the HKEY_LOCAL_MACHINE registry to create our data source, but I'd prefer to use something like rdoRegisterDataSource or SQLConfigDataSource if possible.  Is there another API call which will let me do this?

Thanks.
0
Comment
Question by:jsullivan
  • 4
  • 4
  • 2
  • +4
14 Comments
 
LVL 2

Expert Comment

by:JHausmann
ID: 2605731
Using VB6? If you add the  reference "ODBC Driver & data source Name Fucntions" to your project, you'll gain access to the function "CreateDSN" which is what I believe you want...
0
 
LVL 2

Expert Comment

by:JHausmann
ID: 2605735
<sigh> typo. Should be  "ODBC Driver & Data Source Name Functions"
0
 

Author Comment

by:jsullivan
ID: 2605755
Unfortunately I am using VB5.  I imagine that VB6 must be calling an ODBC32 API call to create the data source, I just don't know what that call is.
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 2

Expert Comment

by:JHausmann
ID: 2605888
One function in ODBC32.dll looks promising, SQLDataSources, but I definitely don't know what it does. Dan Appleman's Win32 API Puzzle Book and Tutorial for Visual Basic Programmers {ISDN 1-893115-0-11}, especially tutorial 9, might be of some help to ya (they have a program, dumpinfo, that works like dumpbin, only better, provided on the CD).
0
 

Author Comment

by:jsullivan
ID: 2605950
SQLDataSources is just used to list the ODBC data sources.  I do not believe it can be used to create them.

Thanks anyway.  Hopefully someone else may know of the API call that I'm looking for.
0
 
LVL 2

Expert Comment

by:JHausmann
ID: 2606106
The package and deployment wizard is using the following .dll when you include the reference I indicated:

ODBCTOOL.DLL

It is part of VB/VStudio 6...
0
 

Author Comment

by:jsullivan
ID: 2606127
Thanks, but I do not have that file.  I'm sure there must be an API call to do what I want to do.  Hopefully someone will know of what it is.
0
 
LVL 12

Expert Comment

by:roverm
ID: 2607771
The API can only create User DSN's.
The solutions I used was popping up the ODBC (control panel) screen and then send some keys (Using SENDKEYS) to let VB enter all the nessecary stuff!

If you want an example I can give it to you monday (it's a my work ;-))!

D'Mzzl!
RoverM
0
 
LVL 6

Expert Comment

by:andyclap
ID: 2608723
Are you sure you need a system DSN?
The ODBC Drive & Data source tools can only create user ones.
In my current project I've resorted to using user DSNs and checking/setting them up each time the app is run.
0
 
LVL 4

Expert Comment

by:wylliker
ID: 2609257
In the ODBC API, the SQLConfigDataSource function can take a parameter called ODBC_ADD_SYS_DSN -wouldn't that mean that you could create a System DSN?
0
 
LVL 1

Expert Comment

by:PatrickVD
ID: 2610569
Hi jsullivan,

There is actually a simple way of creating System DSNs.
All you need is to use the Registry API in order to create an entry in the registry. If you have a look at you current registry under the key HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\odbc.ini, you will find here an entry for all the currently available System DSNs on your machine.
If you want to create an entry programmatically, all you need is to add a new key (and accompanying values) under this registry key.
The values you have to create depend on the type of ODBC driver you will be using... The best thing to figure out what you need, is to create a DSN manually at first, then you can see what entries are needed under this key. You can then write the code to create this type of entries in the registry by code yourself.
If you want to create a User DSN then you should work the same way, but create the entries in the HKEY_CURRENT_USER\SOFTWARE\ODBC\odbc.ini registry key.

Hope this helps....
Let me know if you'd like more details...

Patrick.
0
 
LVL 4

Accepted Solution

by:
gcs001 earned 100 total points
ID: 2612631
Hi,
Here is some code I use in our apps to programmatically create/reconfigure/remove a data source.

Public Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal hwndParent As Long, ByVal fRequest As Long, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long

Public Const ODBC_ADD_DSN = 1          ' Add User DataSource
Public Const ODBC_CONFIG_DSN = 2       ' Configure (edit) User DataSource
Public Const ODBC_REMOVE_DSN = 3       ' Remove User DataSource
Public Const ODBC_ADD_SYS_DSN = 4      ' Add System DataSource
Public Const ODBC_CONFIG_SYS_DSN = 5   ' Configure (edit) System DataSource
Public Const ODBC_REMOVE_SYS_DSN = 6   ' Remove System DataSource

Public Const strORADriver As String = "Oracle73 Ver 2.5"
Public Const strSQLDriver As String = "SQL Server"

Public Const strAttributes = "DSN=MYDSN_Name" & Chr$(0) & "SERVER=DBServer" & Chr$(0) & "UserID=Userid" & Chr$(0) & "Description=My DSN via program" & Chr$(0)

Private Sub cmdRemoveDS_Click()
Dim lngRet as Long

' Remove a datasource
lngRet = SQLConfigDataSource(0&, ODBC_REMOVE_SYS_DSN, strSQLDriver, strAttributes)
       
If lngRet = 0 Then
   MsgBox Err.Description
End If

End Sub

Private Sub cmdCreateDS_Click()
Dim lngRet as Long

' Create/Reconfigure a datasource
' If a DS does not exist the configure will automatically create one
lngRet = SQLConfigDataSource(0&, ODBC_CONFIG_SYS_DSN, strSQLDriver, strAttributes)
       
If lngRet = 0 Then
   MsgBox Err.Description
End If

End Sub


HTH

Regards,
Grant.
0
 

Author Comment

by:jsullivan
ID: 2612694
qcs001, Thanks for your answer.  The ODBC_ADD_SYS_DSN parameter was what I was missing. (wylliker, you mentioned this command as well, but I needed the value of the constant)

PatrickVD, thank you for your comment, but as I mentioned in my original question, I did not want to manually write to the registry.  That is what I was already doing and I wanted to change it to an API call.
0
 
LVL 4

Expert Comment

by:gcs001
ID: 2612721
Glad to be of assistance.

Regards,
Grant Schuleman
South Africa.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

839 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