Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about querytable vs copyfromrecordset
Hello,
I have a question of best practice. My problem is simple. I needed a way to copying the contents of a recordset into a spreadsheet. After doing my research I found 2 ways fo doing this. I could either use the member function CopyFromRecordSet or make a querytable, refresh it or delete it. My initial problem with CopyFromRecordset was it used DAO and my data access class used ADO. Howeverm CopyFromRecordSet apparently can take an ADO record set. The QueryTable object is more robust and seems like the obvious choice for a report that will be updated dynamically. Howerver, I want to create a spreadsheet with no ties to my database. So I wrote 2 identical member functions for my class that both do the same thing. There listed below. The variable cn is a private class varible that represents an ADODB.connection object. I have included both below. My question is being I know how to do it both ways, which way is better? Does one way use less or more memory? Will CopyFromRecordset be depreciated? Is there a performance benifit if I use a querytable that I create via something besides an adodb.recordset? Public Sub createQueryTable(ByRef sht As Worksheet, ByVal strSQL As String) Dim qt As QueryTable Dim rs As Recordset On Error Resume Next cn.Execute "drop view qryTMRC" cn.Execute "create view qryTMRC as " & strSQL If Err.Number < 0 Then MsgBox Err.Description, vbCritical End If Set rs = cn.Execute(CommandText:="qryTMRC", Options:=adCmdTable) Set qt = sht.QueryTables.Add(rs, sht.Application.ActiveCell) qt.Refresh qt.Delete cn.Execute "drop view qryTMRC" On Error GoTo 0 End Sub Public Sub getQueryAsRange(ByRef sht As Worksheet, ByVal strSQL As String) Dim rs As Recordset On Error Resume Next cn.Execute "drop view qryTMRC" cn.Execute "create view qryTMRC as " & strSQL If Err.Number < 0 Then MsgBox Err.Description, vbCritical End If Set rs = cn.Execute(CommandText:="qryTMRC", Options:=adCmdTable) sht.Application.ActiveCell.CopyFromRecordset rs cn.Execute "drop view qryTMRC" On Error GoTo 0 End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about querytable vs copyfromrecordset
I suggested uses Querytable,Because it is EXCEL own thing, with EXCE The coordination quite is good, for example: With CopyFromRecord Whe every one Cell The character cannot be more than 911,etc。 -- bighea ----------------------------------------------------------------------- bighead's Profile: http://www.excelforum.com/member.php...fo&userid=2704 View this thread: http://www.excelforum.com/showthread.php?threadid=46654 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CopyFromRecordset | Excel Programming | |||
CopyFromRecordset Question | Excel Programming | |||
CopyFromRecordset does nothing | Excel Programming | |||
CopyFromRecordset does nothing | Excel Programming | |||
Copyfromrecordset Bug ? | Excel Programming |