VB6, RDC and SQL2000 temp table report

[lovalles]

i want to generate a report from a temporary table generated on SQL, i have made de temp table, but when i try to change the default table of the report it just doesnt work.
i currently have made users tables to generate this report but i dont want to make report from those table i want to be able to generate does temp tables at runtime.
Here is the code:




Set conn = New ADODB.Connection

conn.Open "Provider=SQLOLEDB;Data Source=SERVIDOR4;" _
& "Initial Catalog=Aplicaciones;User ID=ClientUser;Password=USER"


sql = "CREATE TABLE #myTemporaryTable (Col1 CHAR)"
Set rs = conn.Execute(sql)

CrystalReport1.Database.Tables(1).Name = "#myTemporaryTable"
CrystalReport1.Database.Tables(1).SetLogOnInfo "SERVIDOR4", "Aplicaciones", "ClientUser", "USER"
Form2.CRViewer1.ReportSource = CrystalReport1
Form2.CRViewer1.ViewReport
Form2.Show vbModal


Any suggestions?

 

[balves]

Where is the data for the report coming from? All you have seems to be an empty table.

Why not just use a stored procedure?

 

[lovalles]

i know its an empty table, just sepose that i make and SELECT INSERT INTO THE table before i change the table from the report.

thanks
lovalles

 

[balves]

I'm not sure you'll "see" the temporary table. The VB form / Crystal viewer will create a SQL Server process separate from the ADO process that creates the temporary table. You might have to use a global temporary table.

Probably a better idea is to create the report using the Active Data Driver, then pass an ADO recordset to the report at runtime.

 

[lovalles]

How can i do that? can you give me an example or where can i see one?

thanks
lovalles

 

[balves]

A global temporary table has ## in the front of the name of the table instead of one #. A global temp table is visible to all processes, as opposed to just the process that created it. Again, I don't see the point of temp tables here.

You can either use a stored procedure or pass an ADO recordset to the report.

When you create the report, pick the Active Data Driver and paste in the SQL for the report. In VB, create an ADO recordset with the data, then pass the recordset to the report with .SetDataSource

 

[lovalles]

The thing is i make a temp table beacuse a make a process that uses 11 select´s of the same table retreaving some info, so the the is the info i want i just add it in the temp table then the report comes out right away i dont have to do all the process.

if i make a ## table all users connected will be able to use and see the table, what would happend on a multy user report? the temp table for both or multi user will be the same thats why i want to use # table, because only the user that creates the # table can use and see it.

please help
thanks
lovalles

 

[balves]

Perhaps other people have some ideas, but I would strongly recommend a stored procedure as the data source to the report.