?
Solved

Create a system ODBC data source

Posted on 2000-03-10
14
Medium Priority
?
509 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 400 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
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.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

777 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