By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,255 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,667 IT Pros & Developers. It's quick & easy.

Create / enum ODBC source

P: n/a
My app uses an ODBC connection for a .mdb file. I want the app to create the
source if it does not already exist (I copy a blank .mdb file to the users
data store if it doesn't exist).

I see how to do this using SQLConfigDataSource() and that works ok. Thing
is, how to handle if the source already exists.
Is there a way to check & see if the source already exists?

Alternately, if it does exist & I call SQLConfigDataSource() does it mess up
/ break anything? If not, then no need to bother to enum the
sources....though this hardly seems like a good practice.

Also, I have a dumb question - when I choose the DSN to use if VS2005 Data
Sources, is there a way to tell if it is from System or User ?

Thank you,

--
Dale Sampson
http://www.dalesplace.net

I tried using SQLDataSources Lib "odbc32.dll - but haven't been able to get
that to work.
I use this code :
http://vbnet.mvps.org/index.html?cod...atasources.htm.
I get a weird return code (1949695999) from SQLAllocHandle & the returned
handle is zero.
calls to SQLDataSources get return value = 65534.
These do not seem to be Win32 or ODBC error codes.

The code works fine in VB6 so I assume I'm messing up something in the
Declares?

Here's what I used:

'Constant Declaration
Private Const ODBC_ADD_DSN = 1 ' Add data source
Private Const ODBC_CONFIG_DSN = 2 ' Configure (edit) data source
Private Const ODBC_REMOVE_DSN = 3 ' Remove data source
Private Const vbAPINull As Long = 0 ' NULL Pointer
Const ODBC_ADD_SYS_DSN = 4 'Add data source
Const ODBC_CONFIG_SYS_DSN = 5 'Configure (edit) data source
Const ODBC_REMOVE_SYS_DSN = 6 'Remove data source
Private Const SQL_MAX_DSN_LENGTH As Long = 32
Private Const SQL_MAX_DESC_LENGTH As Long = 128
Private Const SQL_SUCCESS As Long = 0
Private Const SQL_FETCH_NEXT As Long = 1
Private Const SQL_NULL_HANDLE As Long = 0
Private Const SQL_HANDLE_ENV As Long = 1
Private Const SQL_ATTR_ODBC_VERSION As Long = 200
Private Const SQL_OV_ODBC3 As Long = 3
Private Const SQL_IS_INTEGER As Long = (-6)
Const SQL_ERROR = (-1)
Const SQL_SUCCESS_WITH_INFO = 1

Private Declare Function SQLDataSources Lib "odbc32.dll" _
(ByVal hEnv As Int32, _
ByVal fDirection As Int16, _
ByVal szDSN As String, _
ByVal cbDSNMax As Int16, _
ByVal pcbDSN As Int16, _
ByVal szDescription As String, _
ByVal cbDescriptionMax As Int16, _
ByVal pcbDescription As Int16) As Int32

Private Declare Function SQLAllocHandle Lib "odbc32.dll" _
(ByVal HandleType As Int16, _
ByVal InputHandle As Int32, _
ByVal OutputHandlePtr As Int32) As Int32

Private Declare Function SQLSetEnvAttr Lib "odbc32.dll" _
(ByVal EnvironmentHandle As Int32, _
ByVal dwAttribute As Int32, _
ByVal ValuePtr As Int32, _
ByVal StringLen As Int32) As Int32

Private Declare Function SQLFreeHandle Lib "odbc32.dll" _
(ByVal HandleType As Int16, _
ByVal Handle As Int32) As Int32

Private Sub GetUserSystemDSN()

Dim hEnv As Long 'handle to the environment
Dim sServer As String
Dim sDriver As String
Dim nSvrLen As Integer
Dim nDvrLen As Integer
Dim Ret As Long = 0
Dim ret2 As Long = 0

'obtain a handle to the environment
ret2 = SQLAllocHandle(SQL_HANDLE_ENV, _
SQL_NULL_HANDLE, hEnv)
'
' ret2 = 1949695999; hEnv=0
'
If ret2 <0 Then

'if successful, set the
'environment for subsequent calls
If SQLSetEnvAttr(hEnv, _
SQL_ATTR_ODBC_VERSION, _
SQL_OV_ODBC3, _
SQL_IS_INTEGER) <0 Then

'
' return val = 1949695998; hEnv = 0
'
'
'set up the strings for the call
sServer = Space$(SQL_MAX_DSN_LENGTH)
sDriver = Space$(SQL_MAX_DESC_LENGTH)

'load the DSN names
Do While Ret = SQL_SUCCESS
Ret = SQLDataSources(hEnv, _
SQL_FETCH_NEXT, _
sServer, _
SQL_MAX_DSN_LENGTH, _
nSvrLen, _
sDriver, _
SQL_MAX_DESC_LENGTH, _
nDvrLen)
'
' ret = 65534
'
'
'add data to the controls
Debug.Print(sServer.Substring(1, nSvrLen))
'List1.AddItem(Left$(sServer, nSvrLen))
'List2.AddItem(Left$(sDriver, nDvrLen))

'repad the strings
sServer = Space$(SQL_MAX_DSN_LENGTH)
sDriver = Space$(SQL_MAX_DESC_LENGTH)

Loop

End If 'If SQLSetEnvAttr

'clean up
Call SQLFreeHandle(SQL_HANDLE_ENV, hEnv)

End If 'If SQLAllocHandle

'since each DSN returned its corresponding
'driver, and a given driver can be used
'for multiple DSN's, remove any adjacent
'duplicates

End Sub
Nov 10 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Hi Dale,

To enumerate ODBC DSN, you need to look at following registry keys:

User DSNs:
HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources -contains
the DSN name and driver that it uses
HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\<dsn name-contains the DSN
configuration details

System DSNs:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources -contains
the DSN name and driver that it uses
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\<dsn name-contains the DSN
configuration details
Calling SQLConfigDataSource with ODBC_ADD_DSN for existing DSN will change
its configuration. If you always want to use new settings for the DSN, then
I think you don't need to check for the DSN's existence first.

As for the VS2005 Data Sources, I'm afraid there're no easy way to tell if
the DSN is System or User. If you think this should be implemented, please
feel free to submit your feedback at
http://connect.microsoft.com/Main/co...ContentID=2220 which
is monitored by our product team directly. Thank you for your understanding.

By the way, you want to be aware following known issue about
SQLConfigDataSource:

#BUG: SQLConfigDataSource Returns TRUE Even Without Registry Permissions
http://support.microsoft.com/kb/294177/

This issue seems still exist on Windows XP SP2, we're sorry for the
inconvience.

Sincerely,
Walter Wang (wa****@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications. If you are using Outlook Express, please make sure you clear the
check box "Tools/Options/Read: Get 300 headers at a time" to see your reply
promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
Nov 13 '06 #2

P: n/a
Thank you Walter. That works ok. Good point with regard to simply writing
the DNS.

Regards,

--
Dale Sampson
http://www.dalesplace.net

"Walter Wang [MSFT]" <wa****@online.microsoft.comwrote in message
news:1O**************@TK2MSFTNGXA01.phx.gbl...
Hi Dale,

To enumerate ODBC DSN, you need to look at following registry keys:

User DSNs:
HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources -contains
the DSN name and driver that it uses
HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\<dsn name-contains the DSN
configuration details

System DSNs:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources -contains
the DSN name and driver that it uses
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\<dsn name-contains the DSN
configuration details
Calling SQLConfigDataSource with ODBC_ADD_DSN for existing DSN will change
its configuration. If you always want to use new settings for the DSN,
then
I think you don't need to check for the DSN's existence first.

As for the VS2005 Data Sources, I'm afraid there're no easy way to tell if
the DSN is System or User. If you think this should be implemented, please
feel free to submit your feedback at
http://connect.microsoft.com/Main/co...ContentID=2220
which
is monitored by our product team directly. Thank you for your
understanding.

By the way, you want to be aware following known issue about
SQLConfigDataSource:

#BUG: SQLConfigDataSource Returns TRUE Even Without Registry Permissions
http://support.microsoft.com/kb/294177/

This issue seems still exist on Windows XP SP2, we're sorry for the
inconvience.

Sincerely,
Walter Wang (wa****@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications. If you are using Outlook Express, please make sure you clear the
check box "Tools/Options/Read: Get 300 headers at a time" to see your
reply
promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
==================================================

This posting is provided "AS IS" with no warranties, and confers no
rights.


Nov 14 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.