D
Dale Sampson
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?code/system/sqldatasources.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
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?code/system/sqldatasources.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