Re: ADO cloned recordset doesn't return field names

From: quartz (quartz_at_discussions.microsoft.com)
Date: 03/16/05 

Date: Wed, 16 Mar 2005 12:59:03 -0800

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 work***? 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.
>
>