Lots of tbls and queries, 100+, and one imbeded value

[pepenacho]

Hi -

I want to soundboard this outhere; maybe someone has seen this before.

I have an MDB file. Lots of tables, lots of queries, on the order of 150+.
Not one of mine. Casing tool was not used, so no entity relationship diagrams
and no data dictionary. The tbls and qrys have many, many fields. Luck would
have it that the darn thing actually works pretty well.

Somewhere in there I have a field set to a value, let's say it's "ABC." I do
know that the value = ABC. Luck would have it, it needs to change to "DEF" -
go figure.

Question is which table or query is storing it? Unfortunately the naming
conventions are not very "self-descriptive," and everything is super-nested.

I tried using the MS Access documenter, dumping it into Adobe PDF and
keyword searching. But that did not work.

Is there anyway to find which tbl or qry stores the value, if all I know is
the value itself? For example, if I could get a massive text dump of all the
properties and contents of table and query objects, that would be awesome,
because then I can keyword search it.

I've never had this before. My stuff is usually very organized, and I'm into
case tools now, so this is a bit frustrating.

Thanks,
Pepe

 

[Dirk Goldgar]

Hi -

I want to soundboard this outhere; maybe someone has seen this before.

I have an MDB file. Lots of tables, lots of queries, on the order of 150+.
Not one of mine. Casing tool was not used, so no entity relationship
diagrams
and no data dictionary. The tbls and qrys have many, many fields. Luck
would
have it that the darn thing actually works pretty well.

Somewhere in there I have a field set to a value, let's say it's "ABC." I
do
know that the value = ABC. Luck would have it, it needs to change to
"DEF" -
go figure.

Question is which table or query is storing it? Unfortunately the naming
conventions are not very "self-descriptive," and everything is
super-nested.

I tried using the MS Access documenter, dumping it into Adobe PDF and
keyword searching. But that did not work.

Is there anyway to find which tbl or qry stores the value, if all I know
is
the value itself? For example, if I could get a massive text dump of all
the
properties and contents of table and query objects, that would be awesome,
because then I can keyword search it.

I've never had this before. My stuff is usually very organized, and I'm
into
case tools now, so this is a bit frustrating.

I'm not sure I understand. Is it really a data value that you are lookling
for, or a field name? If it's a data value, no documenter I ever heard of
is going to tell you about it, but it shouldn't be too hard to write a
little routine to scan every table for a field containing the value. It
could be that one of the "find & replace" utilities, such as "Find and
Replace" or "SpeedFerret" has this feature built-in, though I think of them
mainly as useful for looking for names and property values.

 

[John Spencer MVP]

The value could also be a custom property of the database, if it is not a
value of a field in a record.

Something like this code snippet

Dim db As DAO.Database
Dim prpNew As DAO.Property

Set db = CurrentDb()

Set prpNew = db.CreateProperty("VersionDate", _
dbDate, dteDate)
db.Properties.Append prpNew


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dirk Goldgar]

it shouldn't be too hard to write a little routine to scan every table for
a field containing the value.

Here's a quick and dirty -- and untested -- little routine to search all
tables for a data value. It writes output to the Immediate Window, so if
the value you're looking for is found many times you'll only see the last
255 lines or so of output.

'----- start of code -----
Sub SearchForData(ValueSought As Variant)

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim intValueType As Integer
Dim intFld As Integer
Dim blnTestField As Boolean
Dim aintFieldsToCheck(255) As Integer
Dim intNFields As Integer
Dim I As Integer

intValueType = VarType(ValueSought)
Select Case intValueType
Case vbNull, vbEmpty, vbArray, vbError, vbObject, _
vbDataObject, vbUserDefinedType
Err.Raise 5, , "Invalid data type for search"
End Select

Debug.Print "*** Searching database for "; ValueSought; " ***"
Set db = CurrentDb

For Each tdf In db.TableDefs
If Not (tdf.Name Like "MSys*") Then

Set rs = tdf.OpenRecordset(dbOpenSnapshot)

If rs.EOF = False Then

' Before processing the actual records, make a list of
' all the fields in this recordset of the appropriate type.

intNFields = 0
For I = 0 To rs.Fields.Count - 1

blnTestField = False
Select Case intValueType
Case vbString
Select Case rs.Fields(I).Type
Case dbText, dbMemo, dbChar
blnTestField = True
End Select
Case vbDate
Select Case rs.Fields(I).Type
Case dbDate, dbTime
blnTestField = True
End Select
Case vbBoolean
Select Case rs.Fields(I).Type
Case dbBoolean
End Select
Case Else
Select Case rs.Fields(I).Type
Case dbLong, dbInteger, dbDouble, dbSingle,
dbCurrency, _
dbByte, dbFloat, dbDecimal,
dbNumeric
blnTestField = True
End Select
End Select

If blnTestField Then
aintFieldsToCheck(intNFields) = I
intNFields = intNFields + 1
End If

Next I

' Were there any qualifying fields?
If intNFields > 0 Then

With rs
Do Until .EOF
For I = 0 To (intNFields - 1)

intFld = aintFieldsToCheck(I)
If .Fields(intFld).Value = ValueSought Then
Debug.Print tdf.Name; " : ";
For Each fld In rs.Fields
Debug.Print , fld.Name; "=";
fld.Value
Next fld
Debug.Print
End If
Next I
.MoveNext
Loop
End With

End If

End If

rs.Close
Set rs = Nothing

End If
Next tdf

Debug.Print "*** Search Complete ***"
Set db = Nothing

End Sub
'----- end of code -----

Watch out for possible line wraps caused by the newsreader.

 

[pepenacho]

Thank you both!

The value could also be a custom property of the database, if it is not a
value of a field in a record.

Something like this code snippet

Dim db As DAO.Database
Dim prpNew As DAO.Property

Set db = CurrentDb()

Set prpNew = db.CreateProperty("VersionDate", _
dbDate, dteDate)
db.Properties.Append prpNew


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County