Cannot Create Object

[MikeMcKeown]

Sorry if this is not directly related to this forum - was not sure which one to use.

I am trying to get the code below to run, I am planning to hopefully get this running through a DTS package. However I keep reciveing an error 429 cannot create ActiveX component. I have tried installing regsvr32 c:\windows\system32\cdo32.dll - which seemed to make no difference.

At the moment I am developing it is Visual Basic and plan to move it to VB script in SQL Server DTS when it is working.

Any ideas?


i should reference that this code which taken from

http://www.sqlservercentral.com

Const crEFTPortableDocFormat = 31

' Destination Types, again you can use VB to get other values for const def if you need to.
Const crEDTDiskFile = 1


Dim Conn 'ADO Connection
Dim Comm 'ADO Command
Dim Rcdset 'ADO Recordset

'Dim App
Dim Report 'Crystal Report
Dim DB 'Crystal Database
Dim Tables 'Crystal DatabaseTables
Dim Table1 'Crystal DatabaseTable


'On Error Resume Next ' This example was designed without error handling you need to add your own.

' -------------------- Build and get our recordset for our report. --------------------
Set Conn = CreateObject("ADODB.Connection")
Set Comm = CreateObject("ADODB.Command")
Set Rcdset = CreateObject("ADODB.Recordset")

Conn.Open "Provider=SQLOLEDB.1;Initial Catalog=E_Audit_Database;App=Crystal Export;Data Source=(local)", "**", "*****"



'You're database connection
Comm.ActiveConnection = Conn

Comm.CommandText = "SELECT * from final_reports_last_generated"
'Your sql string or SP.

Set Rcdset = Comm.Execute

' Process for no records to be exported.
If Rcdset.EOF Then
' -------------------- Clean up all our objects we created so far. --------------------

' ADO Objects
Rcdset.Close
Set Rcdset = Nothing
Set Comm = Nothing
Conn.Close
Set Conn = Nothing


End If

' -------------------- On to processing a crystal report. --------------------

' Set our application runtime environment for this process. Make sure you set this to the right runtime value.
Set App = CreateObject("CrystalDesignRuntime.application.8.5") ' This is on a machine with Crystal 8.5
' Open our report.
'Set App = New CRAXDRT.Application
Set Report = App.OpenReport("J:\report1.rpt") ' The path of your crystal report.



' Discard any existing data that was saved with the report.
Report.DiscardSavedData

' Set our base objects for processing this report.
Set DB = Report.Database

Set Tables = DB.Tables
Set Table1 = Tables.Item(1)


' Set our data set to the recordset we pulled.
'Table1.SetPrivateData 3, Rcdset

'Read the records in.
Report.ReadRecords

'Set our export options
'Report.ExportOptions.DestinationType = crEDTDiskFile
'Report.ExportOptions.FormatType = crEFTPortableDocFormat
Report.ExportOptions.DiskFileName = "C:\TESTRCA\diditwork.pdf" ' Path to where to drop PDF.
Report.ExportOptions.PDFExportAllPages = True

' Export report without user interaction.
Report.Export False

' -------------------- Clean up all our objects we created. --------------------

' Crystal Objects
Set Table1 = Nothing
Set Tables = Nothing
Set DB = Nothing
Set Report = Nothing
Set App = Nothing

' ADO Objects
Rcdset.Close
Set Rcdset = Nothing
Set Comm = Nothing
Conn.Close
Set Conn = Nothing

 

[cmmrfrds]

Do you have a reference set to the MDAC library.
'-- Reference Library
'-- Microsoft ActiveX data objects 2.x library needed for ADO