SAP Knowledge Base Article - Public

1199087 - How to programmatically create an ODBC datasource connection using Visual Basic 6

Symptom

How do I programmatically create an ODBC datasource connection using Visual Basic 6?

Environment

  • Crystal Reports XIR2

Resolution



IMPORTANT

The sample code below is meant solely as an example for instructional purposes only. Business Objects can only support Crystal Reports code. Visual Basic code is not supported.

 

Here are the steps:

  1. Call the function and pass the parameter values as follows:

    MakeDSN sDsn,sDriver,sDBFile,1Action,Svr
    Here DSN - Datasource Name, sDriver - ODBC Driver Name, sDBFile - DataBase File Name, 1Action - 1 to Open / 0 to Close, Svr - Server Name


  2. Create the following function:

    Sub MakeDSN (ByVal sDSN As String, ByVal sDriver As String, _ByVal sDBFile As String, ByVal lAction As Long, ByVal Svr As String, _Optional TrustedCon as Variant = "")

    Dim sAttributes$, sDBQ$, regValue$

    Dim lngRet As Long

    Dim hKey As Long

    Dim valueType As Long

    Query the Registry to check whether the DSN is already installed

    Open the key

    If RegOpenKeyEx (HKEY_CURRENT_USER, "Software\ODBC\ODBC.INI\" & sDSN, 0, _KEY_ALL_ACCESS, hKey) = 0 Then

    Zero means no error => Retrieve value of "DBQ" key

    regValue = String$(1024, 0)

    If RegQueryValueEx (hKey, "Database", 0, valueType, regValue, _Len (regValue)) = 0 Then

    Zero means OK, so we can retrieve the value

    If valueType = REG_SZ Then

    sDBQ = Left$(regValue, Instr(regValue, vbNullChar) - 1)

    End If

    End If

    Close the key

    RegCloseKey hKey: sDBQ = ""

    End If

    If (sDBQ = "" And lAction = ODBC_ADD_DSN) Or (sDBQ <> "" And lAction = _ODBC_REMOVE_DSN) Then

    If TrustedCon = "YES" Then

    SAttributes = "DSN=" & sDSN & vbNullChar & "Database=" & sDBFile & vbNullChar & _"Server=" & Svr & vbNullChar & vbNullChar & _"Trusted_Connection=Yes" & vbNullChar

    Else

    sAttributes = "DSN=" & sDSN & vbNullChar & "Database=" & sDBFile & vbNullChar & _"Server=" & Svr & vbNullChar

    End If

    lngRet = SQLConfigDataSource(0&, lAction, sDriver, sAttributes)

    End If

    End Sub

  3. Create the Crystal Report Function in the VB 6.0 Form as follows:

    Public Sub setReportProperties(sMode As String, sReportName As String, sSelFormula As String, _

    ParamArray sFormula() As Variant)

    ' ============================================================================'
    'To Set the Report Properties

    ' sMode - Mode i.e "Print" - Directly to Printer, "View" - To Screen

    ' oCrystal - Name of the Crystal Report Control

    ' sReportName - Report File Name

    ' sSelFormula - Report SELECTion Formula (Optional)

    ' sFormula() - Formulas passed through code with Formula Names

    ' ============================================================================
    Dim iPos%,

    iFIndex%

    With frmMain.crtReport

    .Reset

    .WindowState = crptMaximized

    .WindowShowCloseBtn = True

    .WindowControls = True

    .WindowControlBox = False

    .WindowShowProgressCtls = True

    .WindowShowRefreshBtn = True

    .WindowShowPrintSetupBtn = True

    .WindowShowPrintBtn = True

    .WindowShowSearchBtn = True

    .ProgressDialog = False

    .ReportFileName = gRepPath & "\" & sReportName & ".rpt"

    .LogonInfo(0) = "dsn=" & gDsnName & ";" & "uid=;" & "pwd=" & gDBPass & ";"

    .DiscardSavedData = True

    .ReportTitle = gFirmName

    .SelectionFormula = sSelFormula

    If UCase(sMode) = "VIEW" Then

    .Destination = crptToWindow

    ElseIf UCase(sMode) = "PRINT" Then

    .Destination = crptToPrinter

    End If

    For iPos = 0 To UBound(sFormula)

    .Formulas(iPos) = sFormula(iPos)

    Next

    .Action = 1

    End With

    Exit Sub

    End Sub

TIP

If the code sample is truncated in your browser window, copy and paste the entire block of code into a text editor. Code samples are intentionally formatted this way so they do not wrap. Wrapped code can produce unexpected errors in your application.

Keywords

How to create a ODBC datasource connection using VB 6.0 for crystal report? , 3465684 , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , How To

Product

SAP Crystal Reports XI ; SAP Crystal Reports XI R2