Z
zippy1981
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
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