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]