Solved

Create a system ODBC data source

Posted on 2000-03-10
14
504 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

831 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