ADO cloned recordset doesn't return field names

I am using Windows XP with Office 2003; Microsoft ActiveX Data Objects 2.8
Library;

I am using the following function to execute an ADO query. It works great,
but it doesn't return the field names. How can I get it to do that?

Public Function ADOReturnDisconnectedRecordset(argConnection As String,
argSQL As String) As ADODB.Recordset
'RETURNS A DISCONNECTED ADO RECORDSET TO THE CALLING SUBROUTINE OR FUNCTION
Dim cnADO As ADODB.Connection
Dim rsADO As ADODB.Recordset
Set cnADO = New ADODB.Connection
cnADO.CursorLocation = adUseClient
cnADO.ConnectionString = argConnection
cnADO.CommandTimeout = 0 'NO TIMEOUT
cnADO.Open
Set rsADO = New ADODB.Recordset
rsADO.MaxRecords = 0 '0 = ALL RECORDS
Set rsADO = cnADO.Execute(argSQL)
Set rsADO.ActiveConnection = Nothing 'SEVER CONNECTION
Set ADOReturnDisconnectedRecordset = rsADO.Clone(adLockReadOnly) 'RETURN
CLONE
If rsADO.State = adStateOpen Then rsADO.Close
Set rsADO = Nothing
cnADO.Close
Set cnADO = Nothing
End Function

Please include the correcting line(s). Thanks much in advance.

Hi quartz,

I'm not sure what you mean by this:

> I am using the following function to execute an ADO query. It works
> great, but it doesn't return the field names. How can I get it to do
> that?

Are you using the CopyFromRecordset method to copy the resulting recordset
to a worksheet? Or are you stepping through the records and writing them to
a range that way?

This, in conjunction with your function, worked fine for me (includes the
headers above the data):

Sub test()
Dim rs As ADODB.Recordset
Dim nCol As Integer

Set rs = ADOReturnDisconnectedRecordset("DSN=MyDSN", "SELECT * FROM
[MyTable]")

For nCol = 1 To rs.Fields.Count
Sheets("Sheet1").Cells(1, nCol).Value = rs.Fields(nCol - 1).Name
Next nCol
Sheets("Sheet1").Cells(2, 1).CopyFromRecordset rs
Set rs = Nothing
End Sub


BTW, you shouldn't need this line:

> Set rsADO = New ADODB.Recordset

as the Execute method of the Connection object returns a reference to a
Recordset, you don't need to create a new one first.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


quartz wrote:
> I am using Windows XP with Office 2003; Microsoft ActiveX Data
> Objects 2.8 Library;
>
> I am using the following function to execute an ADO query. It works
> great, but it doesn't return the field names. How can I get it to do
> that?
>
> Public Function ADOReturnDisconnectedRecordset(argConnection As
> String, argSQL As String) As ADODB.Recordset
> 'RETURNS A DISCONNECTED ADO RECORDSET TO THE CALLING SUBROUTINE OR
> FUNCTION Dim cnADO As ADODB.Connection
> Dim rsADO As ADODB.Recordset
> Set cnADO = New ADODB.Connection
> cnADO.CursorLocation = adUseClient
> cnADO.ConnectionString = argConnection
> cnADO.CommandTimeout = 0 'NO TIMEOUT
> cnADO.Open
> Set rsADO = New ADODB.Recordset
> rsADO.MaxRecords = 0 '0 = ALL RECORDS
> Set rsADO = cnADO.Execute(argSQL)
> Set rsADO.ActiveConnection = Nothing 'SEVER CONNECTION
> Set ADOReturnDisconnectedRecordset = rsADO.Clone(adLockReadOnly)
> 'RETURN CLONE
> If rsADO.State = adStateOpen Then rsADO.Close
> Set rsADO = Nothing
> cnADO.Close
> Set cnADO = Nothing
> End Function
>
> Please include the correcting line(s). Thanks much in advance.

Hi Jake,

You are right. It had been awhile since I worked with ADO. I forgot that you
need to specifically loop through the recordset and use ".Name" to get at the
field names. I was thinking that the fields would just be there using
CopyFromRecordset.

I inserted the following portion of your code and it worked fine:

For nCol = 1 To rs.Fields.Count
Sheets("Sheet1").Cells(1, nCol).Value = rs.Fields(nCol - 1).Name
Next nCol

Thanks so much for your help.
"Jake Marx" wrote:

> Hi quartz,
>
> I'm not sure what you mean by this:
>
> > I am using the following function to execute an ADO query. It works
> > great, but it doesn't return the field names. How can I get it to do
> > that?
>
> Are you using the CopyFromRecordset method to copy the resulting recordset
> to a worksheet? Or are you stepping through the records and writing them to
> a range that way?
>
> This, in conjunction with your function, worked fine for me (includes the
> headers above the data):
>
> Sub test()
> Dim rs As ADODB.Recordset
> Dim nCol As Integer
>
> Set rs = ADOReturnDisconnectedRecordset("DSN=MyDSN", "SELECT * FROM
> [MyTable]")
>
> For nCol = 1 To rs.Fields.Count
> Sheets("Sheet1").Cells(1, nCol).Value = rs.Fields(nCol - 1).Name
> Next nCol
> Sheets("Sheet1").Cells(2, 1).CopyFromRecordset rs
> Set rs = Nothing
> End Sub
>
>
> BTW, you shouldn't need this line:
>
> > Set rsADO = New ADODB.Recordset
>
> as the Execute method of the Connection object returns a reference to a
> Recordset, you don't need to create a new one first.
>
> --
> Regards,
>
> Jake Marx
> MS MVP - Excel
> www.longhead.com
>
> [please keep replies in the newsgroup - email address unmonitored]
>
>
> quartz wrote:
> > I am using Windows XP with Office 2003; Microsoft ActiveX Data
> > Objects 2.8 Library;
> >
> > I am using the following function to execute an ADO query. It works
> > great, but it doesn't return the field names. How can I get it to do
> > that?
> >
> > Public Function ADOReturnDisconnectedRecordset(argConnection As
> > String, argSQL As String) As ADODB.Recordset
> > 'RETURNS A DISCONNECTED ADO RECORDSET TO THE CALLING SUBROUTINE OR
> > FUNCTION Dim cnADO As ADODB.Connection
> > Dim rsADO As ADODB.Recordset
> > Set cnADO = New ADODB.Connection
> > cnADO.CursorLocation = adUseClient
> > cnADO.ConnectionString = argConnection
> > cnADO.CommandTimeout = 0 'NO TIMEOUT
> > cnADO.Open
> > Set rsADO = New ADODB.Recordset
> > rsADO.MaxRecords = 0 '0 = ALL RECORDS
> > Set rsADO = cnADO.Execute(argSQL)
> > Set rsADO.ActiveConnection = Nothing 'SEVER CONNECTION
> > Set ADOReturnDisconnectedRecordset = rsADO.Clone(adLockReadOnly)
> > 'RETURN CLONE
> > If rsADO.State = adStateOpen Then rsADO.Close
> > Set rsADO = Nothing
> > cnADO.Close
> > Set cnADO = Nothing
> > End Function
> >
> > Please include the correcting line(s). Thanks much in advance.
>
>

quartz wrote:
> You are right. It had been awhile since I worked with ADO. I forgot
> that you need to specifically loop through the recordset and use
> ".Name" to get at the field names. I was thinking that the fields
> would just be there using CopyFromRecordset.
>
> I inserted the following portion of your code and it worked fine:
>
> For nCol = 1 To rs.Fields.Count
> Sheets("Sheet1").Cells(1, nCol).Value = rs.Fields(nCol -
> 1).Name Next nCol

Great - glad to hear it worked for you.

> Thanks so much for your help.

No problem!

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]