Looping Through All of the Fields in a ADO Record

[LA Lawyer]

I want to loop through each of the fields of a particular record, then
(using the .name of the field as the variable name) assign the value of each
field to the same-named variable.

I recognize that I will need a "FOR EACH" structure, but, among other
things, I can't figure out how to use the field names as the variable names.

I am actually running this in Word 2007 (accessing a single-record Access
2007 ADO recordset), but this seemed to be the better place to post the
question.

Thanks people.

 

[Paul Shapiro]

I want to loop through each of the fields of a particular record, then
(using the .name of the field as the variable name) assign the value of
each field to the same-named variable.

I recognize that I will need a "FOR EACH" structure, but, among other
things, I can't figure out how to use the field names as the variable
names.

I am actually running this in Word 2007 (accessing a single-record Access
2007 ADO recordset), but this seemed to be the better place to post the
question.

Lookup details of the ADO object model. A Recordset has a Fields collection,
in which each item is a Field. Loop through the fields collection for your
recordset.

 

[LA Lawyer]

Yes, I undestood that intellectually. But how is that done? Can you
provide a sample? And how do you assign/"Dim" a field name to be a
variable name? Can you provide same code?

 

[LA Lawyer]

I have figured out how to extract the field names and values (code below) (I
am doing this in Word 2007 for a Access accdb). BUT I STILL DON'T KNOW HOW
TO ASSIGN THE FIELD NAME TO A VARIABLE NAME.

Here is my code (which works):


Sub AllFieldsData()
Dim VCaseID As Integer
VCaseID = 3
Dim cn As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim Rcd As ADODB.Record
Dim i As Integer
Set cn = New ADODB.Connection
SqlStr = "SELECT * FROM Cases WHERE ([CaseID] =" & VCaseID & ")"

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & _
"N:\access data\casesdata.accdb" & ";"
Set Rst = New ADODB.Recordset

With Rst
..Open "Cases", cn, adOpenStatic, adLockOptimistic, adCmdTable ', adCmdText
..Find "Caseid=" & VCaseID
For Each Field In Rst.Fields
Selection.TypeText Text:=.Fields(i).Name & ": " & .Fields(i).Value &
Chr(13) & Chr(10)
i = i + 1
Next
End With
Rst.Close
Set Rst = Nothing
cn.Close
Set cn = Nothing
End Sub

 

[Mike Painter]

I want to loop through each of the fields of a particular record, then
(using the .name of the field as the variable name) assign the value
of each field to the same-named variable.

I recognize that I will need a "FOR EACH" structure, but, among other
things, I can't figure out how to use the field names as the variable
names.
I am actually running this in Word 2007 (accessing a single-record
Access 2007 ADO recordset), but this seemed to be the better place to
post the question.

Thanks people.

What are you trying to accomplish?
Taking an entire record and converting it to a bunch of variables (or
placing them in an array which might be a better choice) removes that
information from it's "natural" environment, SQL and puts it into a world
that usually requires a lot of code.

 

[Ken Snell]

Can you give us a specific example of what you mean by assigning a field
name to a variable name? I'm not understanding what you want to achieve.
--

Ken Snell
http://www.accessmvp.com/KDSnell/

I have figured out how to extract the field names and values (code below)
(I am doing this in Word 2007 for a Access accdb). BUT I STILL DON'T KNOW
HOW TO ASSIGN THE FIELD NAME TO A VARIABLE NAME.

Here is my code (which works):


Sub AllFieldsData()
Dim VCaseID As Integer
VCaseID = 3
Dim cn As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim Rcd As ADODB.Record
Dim i As Integer
Set cn = New ADODB.Connection
SqlStr = "SELECT * FROM Cases WHERE ([CaseID] =" & VCaseID & ")"

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & _
"N:\access data\casesdata.accdb" & ";"
Set Rst = New ADODB.Recordset

With Rst
.Open "Cases", cn, adOpenStatic, adLockOptimistic, adCmdTable ', adCmdText
.Find "Caseid=" & VCaseID
For Each Field In Rst.Fields
Selection.TypeText Text:=.Fields(i).Name & ": " & .Fields(i).Value &
Chr(13) & Chr(10)
i = i + 1
Next
End With
Rst.Close
Set Rst = Nothing
cn.Close
Set cn = Nothing
End Sub

Lookup details of the ADO object model. A Recordset has a Fields
collection, in which each item is a Field. Loop through the fields
collection for your recordset.

 

[Paul Shapiro]

If you mean that you want to programatically create a new variable with the
variable name identical to the field name, I don't believe that can be done.
I don't think you mean something as simple as
Dim myFieldName As String
myFieldName = Fields(i).Name

I have never found a need to dynamically create variables. You can use the
recordset as your data container (it's a good one), or maybe you can use an
array of field names and another array of field values. Can you explain more
clearly what you're trying to do?

I have figured out how to extract the field names and values (code below)
(I am doing this in Word 2007 for a Access accdb). BUT I STILL DON'T
KNOW HOW TO ASSIGN THE FIELD NAME TO A VARIABLE NAME.

Here is my code (which works):


Sub AllFieldsData()
Dim VCaseID As Integer
VCaseID = 3
Dim cn As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim Rcd As ADODB.Record
Dim i As Integer
Set cn = New ADODB.Connection
SqlStr = "SELECT * FROM Cases WHERE ([CaseID] =" & VCaseID & ")"

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & _
"N:\access data\casesdata.accdb" & ";"
Set Rst = New ADODB.Recordset

With Rst
.Open "Cases", cn, adOpenStatic, adLockOptimistic, adCmdTable ', adCmdText
.Find "Caseid=" & VCaseID
For Each Field In Rst.Fields
Selection.TypeText Text:=.Fields(i).Name & ": " & .Fields(i).Value &
Chr(13) & Chr(10)
i = i + 1
Next
End With
Rst.Close
Set Rst = Nothing
cn.Close
Set cn = Nothing
End Sub

Lookup details of the ADO object model. A Recordset has a Fields
collection, in which each item is a Field. Loop through the fields
collection for your recordset.