CopyFromRecordSet vs. OPENROWSET

I created an Access Application in which I run a Stored Procedure as follows:
This code works fine but it takes a long time to execute because it writes the individual records to an Excel spreadsheet on the local C: drive.
Is there a way to run this on the Server side ?

I've heard about the CopyFromRecordsetMethod that copies the contents of an ADO or DAO Recordset object onto a worksheet, beginning at the upper-left corner of the specified range.

I've also heard about using the OPENROWSET in my stored procedure which allows you to write directly to an Excel file on the server (assuming that the driver is installed).

Do you know how I could modify this code to speed up the processing ?


Set com = New ADODB.Command
    With com
       .CommandType = adCmdStoredProc
       .CommandText = "dbo.procFlACS"
       .Parameters.Append .CreateParameter("RptYear", adInteger, adParamInput, 4, intYearSP)
       .ActiveConnection = cn
       Set rstQueryFS = .Execute
    End With

Set objXL = New Excel.Application
      objXL.Workbooks.Add
      strNextFile = GetNextFileName("C:\ACSFALL1.XLS")
      objXL.ActiveWorkbook.SaveAs strNextFile
      Set objWS = objXL.ActiveSheet
      objXL.ActiveSheet.Protect UserInterfaceOnly:=True
     
      For intCol = 0 To rstQueryFS.Fields.Count - 1
        Set fld = rstQueryFS.Fields(intCol)
        objWS.Cells(1, intCol + 1) = fld.Name
      Next intCol
     
      intRow = 2
      Do Until rstQueryFS.EOF
        For intCol = 0 To rstQueryFS.Fields.Count - 1
           If intCol = 15 Then
              objWS.Cells(intRow, intCol + 1).NumberFormat = "000-00-0000"
           End If
           If intCol = 18 Then
              objWS.Cells(intRow, intCol + 1).NumberFormat = "#0.000"
           End If
           If intCol = 24 Then
              objWS.Cells(intRow, intCol + 1).NumberFormat = "@"
           End If
           objWS.Cells(intRow, intCol + 1) = _
              rstQueryFS.Fields(intCol).Value
           objWS.Cells.EntireColumn.AutoFit

        Next intCol
        rstQueryFS.MoveNext
        intRow = intRow + 1
      Loop
      DoCmd.Hourglass False
       
      objXL.Visible = True
zimmer9Asked:
Who is Participating?
 
arbertCommented:
"User cannot change file location in SPs. "

Sure they can....Just another parm to pass....

I agree with nmcdermaid , you don't want to install any client apps on your server.

I guess my question would be, why are you using an Access application to write records to excel?!?!??!  Thought about using DTS to export the records to excel?
0
 
ispalenyCommented:
1. CopyFromRecordSet
It is Excel method, and it is run on a client side.
http://www.devx.com/tips/Tip/17303

2.OPENROWSET
It is SQL Server clause, and it is run on a server side. User cannot change file location in SPs.
0
 
nmcdermaidCommented:
Think about the path your data has to take.

If your Excel file is on the SQL server:

-using client side code means the data makes a trip to the client then a trip back to the server (2 trips).
-using server side code means the data stays on the server (0 trips).



If your Excel file is on the client:

-using client side code means the data makes one trip to the client (1 trip).
-using server side code means the data makes one trip to the client (1 trip).



If your Excel file is on neither:

-using client side code means the data makes a trip to the client then a trip to your destination (2 trips).
-using server side code means the data makes a trip to your destination (1 trip).



Depending on your network speed, and how much data you have, the number of trips can make a huge difference.


You can use CopyFromRecordsetMethod on the server but you have to install Excel on your server (not recommended)


I would always recommend doing it on the server because then no one ever needs some funky code installed on their client machine.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ispalenyCommented:
User cannot change file location in SPs, unless all ad-hoc queries are enabled on the OLEDB provider. In this scenario, the user is able to read and sometimes also modify any file readable by Jet driver including text files on SQL Server. Some people can think this would be a real security hole, or I missed something?
0
 
arbertCommented:
"User cannot change file location in SPs, unless all ad-hoc queries are enabled on the OLEDB provider."

You don't have to enable adhoc queries to accomplish something so simple...No security hole.....
0
 
ispalenyCommented:
...No security hole.....

arbert,
you should look at traffic after one of your old answers http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_20554649.html . I think it is self-explaining. Later I opened a question about it, but nobody responded, so it was deleted.

Without ad-hoc queries enabled, a normal user (not sysadmin) is even not able to run OPENROWSET.
0
 
nmcdermaidCommented:
If you blindly pass through a data source, sure its a security issue.

But if you write secure code that checks what is passed thruogh, it's not a security issue.

0
 
arbertCommented:
"If you blindly pass through a data source, sure its a security issue."

What kind of security hole is an output data source, I guess I don't get the point....
0
 
nmcdermaidCommented:
I was referring more to the SQL injection thing.

If you pass this directly through as the source file argument

fictional file' GO DROP DATABASE VitalProductionDB GO --

Then he might have a problem.




But thats got nothing to do with adhoc queries, or wether or not you can pass a parameter to the openrowset function.




So in response to the statement that enabling adhoc queries is a security issue, I think it's only a security issue if your security design allows it to be. There are many ways to stop it such as disabling write access to files etc.
0
 
arbertCommented:
"So in response to the statement that enabling adhoc queries is a security issue, I think it's only a security issue if your security design allows it to be. There are many ways to stop it such as disabling write access to files etc."

yep, agree...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.