Getting Field Values

[Always Learning]

Hi Guys,
Can you help me?
I want to loop through all the fields in all the tables looking for a
certain value in a field. The piece of code below can get me the table name
and the field name but I can not find out how to get the field value.
(tdf.Name gets me the table name ) (fld.Name gets me the field name)
fld.value will not get me the field value.
does anyone know?

Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Set db = DBEngine(0)(0)
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then
Debug.Print "** " & tdf.Name
For Each fld In tdf.Fields
Debug.Print fld.Name
If InStr(1, fld.Value,"UPGRADE") Then
MsgBox (" Value = " & fld.Value & " Table = " & tdf.Name)
End If
Next fld
End If
Next tdf

Your help is greatly appreciated.

Best Regards,

Steve Wilson.

 

[Douglas J Steele]

Going through the TableDefs and Fields collections like that only gets you
to the metadata, not to the data itself.

You need to actually query the tables themselves.

One approach would be to replace

If InStr(1, fld.Value,"UPGRADE") Then
MsgBox (" Value = " & fld.Value & " Table = " & tdf.Name)
End If

with

If fld.Type = dbText Or fld.Type = dbMemo Then
If DCount("*", "[" & tdfName & "]", "[" & fld.Name & "] Like
'*UPGRADE*'") > 0 Then
MsgBox ("UPGRADE appears at least once in " & fld.Name & " in
Table = " & tdf.Name)
End If
End If

(You can't have text in numeric fields, so you need to check the field type
before looking for the word UPGRADE in the field. The inclusion of the "["
and "]" around the table and field names is to handle embedded blanks. If
you're positive you don't have any embedded blanks in your names, you can
leave them out.)

On the other hand, this really is an unusual requirement. If you wanted to
post back with an explanation of why you need to do this, we should be able
to provide you with a better approach.

 

[Always Learning]

Hi Douglas,
Thanks for the reply. That worked great.
I am using it because an application that I developed uses an Access
database with around forty tables in it. If I do not change anything in the
development for quite a while and then I need to make changes to the data, I
can not remember where the fields are that hold the string I want to change.
I do have a couple of other questions.
How can I make the string I am looking for user inputable with a textbox and
how can I update the value to a new value (user inputable)

Thanks again for your help. It's good of you to share your knowledge.

Best Regards,

Steve Wilson.

Going through the TableDefs and Fields collections like that only gets you
to the metadata, not to the data itself.

You need to actually query the tables themselves.

One approach would be to replace

If InStr(1, fld.Value,"UPGRADE") Then
MsgBox (" Value = " & fld.Value & " Table = " & tdf.Name)
End If

with

If fld.Type = dbText Or fld.Type = dbMemo Then
If DCount("*", "[" & tdfName & "]", "[" & fld.Name & "] Like
'*UPGRADE*'") > 0 Then
MsgBox ("UPGRADE appears at least once in " & fld.Name & " in
Table = " & tdf.Name)
End If
End If

(You can't have text in numeric fields, so you need to check the field type
before looking for the word UPGRADE in the field. The inclusion of the "["
and "]" around the table and field names is to handle embedded blanks. If
you're positive you don't have any embedded blanks in your names, you can
leave them out.)

On the other hand, this really is an unusual requirement. If you wanted to
post back with an explanation of why you need to do this, we should be able
to provide you with a better approach.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Guys,
Can you help me?
I want to loop through all the fields in all the tables looking for a
certain value in a field. The piece of code below can get me the table name
and the field name but I can not find out how to get the field value.
(tdf.Name gets me the table name ) (fld.Name gets me the field name)
fld.value will not get me the field value.
does anyone know?

Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Set db = DBEngine(0)(0)
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then
Debug.Print "** " & tdf.Name
For Each fld In tdf.Fields
Debug.Print fld.Name
If InStr(1, fld.Value,"UPGRADE") Then
MsgBox (" Value = " & fld.Value & " Table = " & tdf.Name)
End If
Next fld
End If
Next tdf

Your help is greatly appreciated.

Best Regards,

Steve Wilson.

 

[Douglas J Steele]

For point 1, you could use the InputBox function to get the string:

Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Dim strInput As String

strInput = InputBox("For what text do you want to search?", "Text Search",
"")

If Len(strInput) > 0 then
Set db = DBEngine(0)(0)
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then
Debug.Print "** " & tdf.Name
For Each fld In tdf.Fields
Debug.Print fld.Name
If fld.Type = dbText Or fld.Type = dbMemo Then
If DCount("*", "[" & tdfName & "]", "[" & fld.Name & "] " & _
Like '*" & strInput & "*'") > 0 Then
MsgBox ("UPGRADE appears at least once in " & _
fld.Name & " in Table = " & tdf.Name)
End If
End If
Next fld
End If
Next tdf
Else
' The user didn't provide a string to search for.
End If

For point 2, you'd have to take a different approach than above: domain
aggregate functions (such as DCount or DLookup) cannot be used to change
data.

You could try creating and runnning UPDATE queries in code, or you could
open a recordset of all rows where the text appears in the field, and update
row-by-row. UPDATE queries are usually far more efficient, but depending on
what text changes you need to make, the SQL might be a little hairy.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas,
Thanks for the reply. That worked great.
I am using it because an application that I developed uses an Access
database with around forty tables in it. If I do not change anything in the
development for quite a while and then I need to make changes to the data, I
can not remember where the fields are that hold the string I want to change.
I do have a couple of other questions.
How can I make the string I am looking for user inputable with a textbox and
how can I update the value to a new value (user inputable)

Thanks again for your help. It's good of you to share your knowledge.

Best Regards,

Steve Wilson.

Going through the TableDefs and Fields collections like that only gets you
to the metadata, not to the data itself.

You need to actually query the tables themselves.

One approach would be to replace

If InStr(1, fld.Value,"UPGRADE") Then
MsgBox (" Value = " & fld.Value & " Table = " & tdf.Name)
End If

with

If fld.Type = dbText Or fld.Type = dbMemo Then
If DCount("*", "[" & tdfName & "]", "[" & fld.Name & "] Like
'*UPGRADE*'") > 0 Then
MsgBox ("UPGRADE appears at least once in " & fld.Name & " in
Table = " & tdf.Name)
End If
End If

(You can't have text in numeric fields, so you need to check the field type
before looking for the word UPGRADE in the field. The inclusion of the "["
and "]" around the table and field names is to handle embedded blanks. If
you're positive you don't have any embedded blanks in your names, you can
leave them out.)

On the other hand, this really is an unusual requirement. If you wanted to
post back with an explanation of why you need to do this, we should be able
to provide you with a better approach.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Guys,
Can you help me?
I want to loop through all the fields in all the tables looking for a
certain value in a field. The piece of code below can get me the table name
and the field name but I can not find out how to get the field value.
(tdf.Name gets me the table name ) (fld.Name gets me the field name)
fld.value will not get me the field value.
does anyone know?

Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Set db = DBEngine(0)(0)
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then
Debug.Print "** " & tdf.Name
For Each fld In tdf.Fields
Debug.Print fld.Name
If InStr(1, fld.Value,"UPGRADE") Then
MsgBox (" Value = " & fld.Value & " Table = " & tdf.Name)
End If
Next fld
End If
Next tdf

Your help is greatly appreciated.

Best Regards,

Steve Wilson.