Fields Properties

[Jose Perdigao]

I create a recordset:
The recordset comes from view or sp.
dim cn as adodb.connection
dim rc as adodb.recordset
set cn = currentproject.connection
set rc = New adodb.recordset
rc.Open "Select...", cn

Now I would like to get the properties for each field in a recordset (rc)
Basically, if the data type is integer, long, text ect.
Is it possible via vba to get the properties?

thanks
jcp

 

[Allen Browne]

You can examine the Type of each field in the recordset.

This kind of thing:
Dim rs As New ADODB.Recordset
Dim fld As ADODB.Field
Dim strSql As String

strSql = "SELECT MyTable.* FROM MyTable;"
rs.Open strSql, CurrentProject.Connection

For Each fld In rs.Fields
Debug.Print fld.Name, fld.Type
Next
set fld = Nothing
rs.close
Set rs = Nothing

For an interpretation of the field type numbers, see:
http://allenbrowne.com/ser-49.html

 

[Jose Perdigao]

Thanks Allen,
It works perfectly,

Another question aboutn adodb.recordset.

Normally to generate a recordset I use:

dim cn as adodb.connection
dim rs as adodb.recordset

set cn =currentproject.connection
set rs = New adodb.recordset

rs.open "strsql", cn

Questions.
If I use:
dim cn as adodb.connection
dim rs as New adodb.recordset
rs.open "strsql", currentproject.connection

I reduce lines, is better? the speed is the same?

Is really need to add the following lines?

set fld = Nothing
rs.close
Set rs = Nothing

Regards
jcp

 

[Douglas J. Steele]

No, it's not better:

http://search.live.com/results.aspx?mkt=en-ca&FORM=TOOLBR&q=+vb++"dim+as+new"

 

[Jose Perdigao]

thanks Dougals

No, it's not better:

http://search.live.com/results.aspx?mkt=en-ca&FORM=TOOLBR&q=+vb++"dim+as+new"