Solved

Create a system ODBC data source

Posted on 2000-03-10
14
502 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

747 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

12 Experts available now in Live!

Get 1:1 Help Now