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 SQLConfigDataSo urce() 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 SQLConfigDataSo urce() does it mess up
/ break anything? If not, then no need to bother to enum the
sources....thou gh 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_DS N = 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_LEN GTH As Long = 32
Private Const SQL_MAX_DESC_LE NGTH 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_V ERSION 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_WIT H_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 cbDescriptionMa x 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 EnvironmentHand le 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 GetUserSystemDS N()
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(h Env, _
SQL_ATTR_ODBC_V ERSION, _
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_LEN GTH, _
nSvrLen, _
sDriver, _
SQL_MAX_DESC_LE NGTH, _
nDvrLen)
'
' ret = 65534
'
'
'add data to the controls
Debug.Print(sSe rver.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(S QL_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
