How to tell RDBMS opened by ODBC connection?

S

Schof

Registered User.
Local time
Today, 14:17
Joined
Nov 15, 2002
Messages
44
I have a form defined in which I prompt the user to select an ODBC data source using code similar to the following:

Code: 
Dim wrkODBC As Workspace
Dim conPubs As Connection

' Create ODBCDirect Workspace object.
Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
Set conPubs = wrkODBC.OpenConnection("DBConnection", dbDriverPrompt, True, "ODBC;DSN=;UID=;PWD=")
What I need help with is figuring out which RDBMS the driver belongs to (I.e. is it for oracle, db2, etc?). The RDBMS version is not as much an issue for me but would be nice if I could get that as well. Has anyone run into this or seen information on this topic that might guide me in the right direction?
 
Put the code below in a procedure or function.

Code: 
Dim strSQLInfo as String
Dim strConnStrIn as String
Dim strConnStrOut as String

strSQLInfo = GetSQLInfo(SQL_DBMS_NAME, strConnStrIn, strConnStrOut)
Either strSQLInfo will be Empty (if the referenced function was unable to get the information) or I just saved you a sh*tload of work. :)

Note: The function returns the complete driver connection string. You can make subsequent calls to GetSQLInfo and by replacing "strConnStrIn" with "strConnStrOut" you can avoid the user getting prompted for the connection information.


Put the following code in a module:

Code: 
Option Explicit

Public Declare Function SQLAllocHandle Lib "odbc32.dll" _
  (ByVal HandleType As Integer, _
   ByVal InputHandle As Long, _
   OutputHandlePtr As Long) As Long
   
Public Declare Function SQLDataSources Lib "odbc32.dll" _
  (ByVal hEnv As Long, _
   ByVal fDirection As Integer, _
   ByVal szDSN As String, _
   ByVal cbDSNMax As Integer, _
   pcbDSN As Integer, _
   ByVal szDescription As String, _
   ByVal cbDescriptionMax As Integer, _
   pcbDescription As Integer) As Long

Public Declare Function SQLConnect Lib "odbc32.dll" ( _
     ByVal hdbc As Long, _
     ByVal ServerName As String, _
     ByVal ServerNameLen As Integer, _
     ByVal UserName As String, _
     ByVal UserNameLen As Integer, _
     ByVal Authentication As String, _
     ByVal AuthenticationLen As Integer) As Long

Public Declare Function SQLDriverConnect Lib "odbc32.dll" ( _
     ByVal hdbc As Long, _
     ByVal hwnd As Long, _
     ByVal szConnStrIn As String, _
     ByVal cbConnStrIn As Integer, _
     ByVal szConnStrOut As String, _
     ByVal cbConnStrOutMax As Integer, _
     pcbConnStrOut As Integer, _
     ByVal fDriverCompletion As Integer) As Long
     
Public Declare Function SQLFreeHandle Lib "odbc32.dll" _
  (ByVal HandleType As Integer, _
   ByVal Handle As Long) As Long
                                                  
Public Declare Function SQLGetInfo Lib "odbc32.dll" ( _
     ByVal hdbc As Long, _
     ByVal fInfoType As Integer, _
     ByVal rgbInfoValue As String, _
     ByVal cbInfoValueMax As Integer, _
     ByRef pcbInfoValue As Integer) As Long
     
Public Declare Function SQLSetEnvAttr Lib "odbc32.dll" _
  (ByVal EnvironmentHandle As Long, _
   ByVal dwAttribute As Long, _
   ByVal ValuePtr As Long, _
   ByVal StringLen As Long) As Long
   
' ***********************************************************************
' ODBC API Declarations
' ***********************************************************************

' SQL Return Codes
Public Const SQL_ERROR As Long = -1
Public Const SQL_INVALID_HANDLE As Long = -2
Public Const SQL_SUCCESS As Long = 0
Public Const SQL_SUCCESS_WITH_INFO As Long = 1
Public Const SQL_NO_DATA_FOUND As Long = 100

' Data Source Request Flags
Public Const ODBC_ADD_DSN = 1            ' Add a new user data source
Public Const ODBC_CONFIG_DSN = 2         ' Configure (modify) an existing user data source
Public Const ODBC_REMOVE_DSN = 3         ' Remove an existing user data source
Public Const ODBC_ADD_SYS_DSN = 4        ' Add a new system data source
Public Const ODBC_CONFIG_SYS_DSN = 5     ' Modify an existing system data source
Public Const ODBC_REMOVE_SYS_DSN = 6     ' Remove an existing system data source
Public Const ODBC_REMOVE_DEFAULT_DSN = 7 ' Remove the default data source

' SQL Fetch Directions
Public Const SQL_FETCH_NEXT As Long = &H1&
Public Const SQL_FETCH_FIRST As Long = &H2&
Public Const SQL_FETCH_LAST As Long = &H4&
Public Const SQL_FETCH_PRIOR As Long = &H8&
Public Const SQL_FETCH_ABSOLUTE As Long = &H10&
Public Const SQL_FETCH_RELATIVE As Long = &H20&
Public Const SQL_FETCH_RESUME As Long = &H40&
Public Const SQL_FETCH_BOOKMARK As Long = &H80&

' Driver Completion Options
Public Const SQL_DRIVER_NOPROMPT As Long = 0
Public Const SQL_DRIVER_COMPLETE As Long = 1
Public Const SQL_DRIVER_PROMPT As Long = 2
Public Const SQL_DRIVER_COMPLETE_REQUIRED As Long = 3

' SQL Handle Types
Public Const SQL_NULL_HANDLE As Long = 0
Public Const SQL_HANDLE_ENV As Long = 1
Public Const SQL_HANDLE_DBC As Long = 2
Public Const SQL_HANDLE_STMT As Long = 3
Public Const SQL_HANDLE_DESC As Long = 4
Public Const SQL_HANDLE_SENV As Long = 5

'Driver Information Types
Public Const SQL_DATA_SOURCE_NAME As Long = 2
Public Const SQL_DRIVER_NAME As Long = 6
Public Const SQL_DRIVER_VER As Long = 7
Public Const SQL_ODBC_VER As Long = 10
Public Const SQL_SERVER_NAME As Long = 13
Public Const SQL_DBMS_NAME As Long = 17
Public Const SQL_DBMS_VER As Long = 18
Public Const SQL_USER_NAME As Long = 47
Public Const SQL_DRIVER_ODBC_VER As Long = 77

' Environment Attributes
Public Const SQL_ATTR_ODBC_VERSION As Long = 200

Public Const SQL_OV_ODBC3 As Long = 3
Public Const SQL_IS_INTEGER As Long = (-6)

Public Function GetSQLInfo(ByVal intInfoType As Integer, Optional ByRef strConnStrIn As String, _
                                                         Optional ByRef strConnStrOut As String) As String
On Error GoTo Err_GetSQLInfo

    Dim strInfoValue As String: strInfoValue = Empty
    Dim intInfoValueLength As Integer
    Dim intConnStrOutLength As Integer
    Dim hEnv As Long
    Dim hDBConn As Long
    Dim lngReturnValue As Long
    
    ' Allocate environment handle
    If SQLAllocHandle(SQL_HANDLE_ENV, _
                      SQL_NULL_HANDLE, _
                      hEnv) <> 0 Then
    
        ' Set environment attribute
        If SQLSetEnvAttr(hEnv, _
                         SQL_ATTR_ODBC_VERSION, _
                         SQL_OV_ODBC3, _
                         SQL_IS_INTEGER) <> 0 Then
        
            ' Allocate connection handle
            If SQLAllocHandle(SQL_HANDLE_DBC, _
                              hEnv, _
                              hDBConn) <> 0 Then
            
                strConnStrOut = Space$(1024)
                
                lngReturnValue = SQLDriverConnect(hDBConn, _
                                                  Screen.ActiveForm.hwnd, _
                                                  strConnStrIn, _
                                                  Len(strConnStrIn), _
                                                  strConnStrOut, _
                                                  1024, _
                                                  intConnStrOutLength, _
                                                  SQL_DRIVER_COMPLETE_REQUIRED)
                
                Select Case lngReturnValue
                Case SQL_SUCCESS, SQL_SUCCESS_WITH_INFO
                    strConnStrOut = Left$(strConnStrOut, intConnStrOutLength)
                Case Else ' Unable to connect to driver
                    GoTo Exit_GetSQLInfo
                End Select
                
                strInfoValue = Space$(255)
                
                lngReturnValue = SQLGetInfo(hDBConn, _
                                            intInfoType, _
                                            strInfoValue, _
                                            255, _
                                            intInfoValueLength)
                
                Select Case lngReturnValue
                Case SQL_SUCCESS, SQL_SUCCESS_WITH_INFO
                    strInfoValue = Left$(strInfoValue, intInfoValueLength)
                Case Else ' Unable to get info
                    strInfoValue = Empty
                    GoTo Exit_GetSQLInfo
                End Select
            End If ' SQLAllocHandle
        End If ' SQLSetEnvAttr
    End If ' SQLAllocHandle
                     
Exit_GetSQLInfo:
    GetSQLInfo = strInfoValue
    
    If hDBConn <> 0 Then
        ' Release connection handle
        Call SQLFreeHandle(SQL_HANDLE_DBC, hDBConn)
    End If
    
    If hEnv <> 0 Then
        ' Free environment handle
        Call SQLFreeHandle(SQL_HANDLE_ENV, hEnv)
    End If
    Exit Function

Err_GetSQLInfo:
    MsgBox Err.Description
    Resume Exit_GetSQLInfo

End Function
 
Thanks, Schof, that was very helpful! In your code, you reference Screen.ActiveForm.hwnd. I have a little utility I use in Excel to read SQL using ADO and then dump the data into a spreadsheet. What would I use in place of Screen.ActiveForm.hwnd? Thanks for your help!
 
Haven't done it using Excel but looking at the MSDN web site it looks like you can pass in a null pointer if either the window handle is not applicable or SQLDriverConnect will not present any dialog boxes.
 
You must log in or register to reply here.

Similar threads

A
Solved How To Read IP address From Table
Replies
7
Views
1,325
Ashfaque
A
A
ODBC Error for one user after upgrading to Windows 10 Access 2016
Replies
6
Views
1,734
Alc
A
K
problem with odbc connection or vba code
Replies
5
Views
1,612
sonic8
S
R
Error Connecting to z/OS DB2
Replies
5
Views
2,105
sonic8
S
S
Best way to distribute SQL Server Express Edition with Access application.
Replies
6
Views
1,555
kevlray
kevlray

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom