Crystal Reports in Microsoft Access

[htechhost]

I have an MS Access data project (front end access 2003 and back sql) All the data is on an Sql 2000 server. I have crystal reports 10 (developer) and I was able to intergrate through access the ability to display a basic crystal report with static data. (using a form and the crystal viewer activex script in access) For the life of me I can't find anything on connecting the crystal reports to the sql server in access vba code. Does anyone know the proper way to open a connection to a back (sql or odbc) connection through microsoft access so the report can communicate with the sql server? I will probally pass varibles into the report at some point but right now I just need a connection to the database. At some point we are going to move into .net so I want to start writing the reports in crystal.

On load event of ms access form: (a lot is commented out as I'm trying to get this to work)
----------------------------
Private Sub Form_Load()
Dim crxApplication As New CRAXDRT.Application
Dim crxReport As CRAXDRT.Report
Dim CRXParamDef As CRAXDRT.ParameterFieldDefinition
Dim CRXParamDefs As CRAXDRT.ParameterFieldDefinitions
Dim CRXTables As CRAXDRT.TableLinks
Dim CRXTable As CRAXDRT.TableLink

' Set crxrpt = CRxApp.OpenReport("D:\reports\rptTest.rpt")

' CrystalReports = crxrpt
' CRViewer1.ViewReport



'destroying the report object

Set crxReport = Nothing


'Using the .OpenReport method from the Application object to set your Report object to a RPT file

Set crxReport = crxApplication.OpenReport("F:\rptMonarch\rptTest.rpt")
Set CRXParamDefs = crxReport.ParameterFields

'For Each CRXParamDef In CRXParamDefs
' 'With CRXParamDef
'' For iIndex = 0 To 9
'' If aryParms(iIndex).ParmName <> "" Then
'' If UCase(aryParms(iIndex).ParmName) = UCase(CRXParamDef.ParameterFieldName) Then
'' Select Case UCase(aryParms(iIndex).ParmType)
'' Case "DATE"
'' .AddCurrentValue CDate(aryParms(iIndex).ParmValue)
'' Case "STRING"
'' .AddCurrentValue aryParms(iIndex).ParmValue
'' Case "INTEGER"
' CRXParamDef.AddCurrentValue CLng(152)
'' End Select
'' End If
'' End If
'' Next iIndex
' 'End With
'Next CRXParamDef

Set CRXTables = crxReport.Database.Tables
For Each CRXTable In CRXTables

Next CRXTable

'connection to database--not working


crxReport.Database = "Provider=SQLOLEDB;Data Source=SQLSERVERNAME; database=DBNAME;User Id = sa; Password = PASSWORD;"
crxReport.Action = 0
crxReport.ReadRecords

'accessing the database and reading records



'Setting the ReportSource of the CrViewer control to the Report object

Me!CrystalReports.ReportSource = crxReport

'view the updated report

Me!CrystalReports.ViewReport

'zoom is set to 56%

Me!CrystalReports.Zoom (100)
Me!CrystalReports.Left = 50
Me!CrystalReports.Top = 50
Me!CrystalReports.Width = 15500
Me!CrystalReports.Height = 10800
'do events first before continuing in order to avoid application crashes

'While Me!Crviewer1.IsBusy
'
' DoEvents
'
'Wend

End Sub
--------------------

 

[htechhost]

Any one use MS Access and have Crystal reports? I would just like to see some example code how you call the reports and how they connect to the backend sql server. Thanks!