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

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

• Crystal Reports XIR2

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.

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

SAP Crystal Reports XI ; SAP Crystal Reports XI R2