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:
-
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 - 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
- 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
H o w t o c r e a t e a O D B C d a t a s o u r c e c o n n e c t i o n u s i n g V B 6 . 0 f o r c r y s t a l r e p o r t ? , 3 4 6 5 6 8 4 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To