Create Excel file from VB with parameters

[GShen]

I am using Access 2000 and SQL 2000. I am in a catch 22. I need to be able to create a file in order to send it via Email. I can do it by using the following:
DoCmd.OutputTo acOutputStoredProcedure, "spName", acFormatXLS, stFileName, False
This all works fine. However, my stored procedure has 2 parameters I need to pass. There are no parameter options with this command.
I can pass parameters to create a recordset but how do I then get the recordset to my output file.
It looks like I have to create a table (I didn't really want to do this) by the stored procedure and then do a transfer of the table to my file name.
Do anyone have any ideas how I can skip the step of creating the table?
Much appreciated. Hope I didn't confuse anyone. (It always seems clearer when you create the thread).
Thanks again in adavance

 

[GoodOmens]

You could open open your stored procedure using a recordset, then use the recordset with the OutputTo.

Sub Example()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsdesc As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT [mytable].field1 " & _
"FROM [mytable] " & _
"WHERE ((([mytable].ID)=1))", dbOpenSnapshot)
rs.MoveFirst
DoCmd.OutputTo acOutputQuery, rs.Name, acFormatXLS, stFileName, False
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

 

[GShen]

Thanks,
This is close to what I need. I have a few questions/problems.

1)The stored procedure is about 200 lines long. Can I execute the stored procedure without having the code in line? I don't want to change the front end everytime I have to make a change to the stored procedure.

If I am reading your code correctly, you are loading the recordset with 'Select................', not the records from which the stored procedure would return. Then when you perform rst.movefirst it gets this entry and loads it in rs.name. This is what gets passed to acoutputquery in order to execute.

2)As a side note, are the following 3 lines standard practice for clearing memory? I do not told to perform this. However, I have seen code like this all over the place so I am starting to get concerned. What happens when you do not do it.

rs.Close
Set rs = Nothing
Set db = Nothing

 

[GShen]

Found my answer. All I had to do was create a string with the parameters in it and then use the string name within the Docmd. I thought I had done this already but we all know how we thought we did things and then you do it again and it works. Obviously I had something wrong the 1st 50 times.

Thanks again GoodOmens's.

Could you please comment on bullet #2 above. Was this something you needed for DAO? I am using ADO.

 

[Nikita6003]

Hi GShen

Even though your rs and db variables are local to the procedure, it's good practice to release all object variables before quitting the routine.
If you don't, you can end up with memory problems, funny "ghost" threads hanging about & other unpleasentnesses. It's more a case of doing it "just in case" but it's cleared up some funny messes for me in the past ;-)

Cheers
Nikki

 

[GShen]

Thanks Nikki. Obviously, I cannot change all the code today, but as I go through procedures I will make the necessary changes and also make this a practice of the future. I know, this stuff is funny some times.

 

[evilmousse]

This code is very helpful, but I've got one minor caveat: I'm using an ADO recordset, and it has no .name property. I'm sure there's some self-referencing function or member i can call, but I'm not finding it yet.

I need to pass the ADO recordset to the DoCmd.OutputTo command's objectname parameter.

Any advice?


-evilme