Search all tables in Acces DB 1

[johnny45]

I need to search for a record but I dont know the table name or field. Is there a way to do this ?

 

[Ed2020]

Do you mean you want to search for a specific value in a table, but you don't know the field name or which table it appears in?

If so the following function should do you want. Be aware that it may well take a while to run dependent on the size of your database.

Public Function FindVal(ByVal varSearchVal As Variant)
    Dim ThisDB As DAO.Database
    Dim TDef As DAO.TableDef
    Dim rstSearchMe As DAO.Recordset
    Dim MyField As DAO.Field
    Dim lngRecordNumber As Long
    Dim strResults As String
    
    Set ThisDB = CurrentDb
    
    For Each TDef In ThisDB.TableDefs
        If Left$(TDef.Name, 4) <> "MSys" Then
            lngRecordNumber = 1
            Set rstSearchMe = ThisDB.OpenRecordset(TDef.Name, dbOpenSnapshot)
            
            With rstSearchMe
                .MoveFirst
                Do While Not .EOF
                    For Each MyField In rstSearchMe.Fields
                        If rstSearchMe(MyField.Name).Value = varSearchVal Then
                            strResults = strResults & "Value found in table " & TDef.Name & " in field " & MyField.Name & " at record number " & lngRecordNumber & "." & vbCrLf
                        End If
                    Next MyField
                    .MoveNext
                    lngRecordNumber = lngRecordNumber + 1
                Loop
            End With
        End If
    Next TDef
    
    FindVal = strResults
End Function

Ed Metcalfe.

Please do not feed the trolls.....

 

[PHV]

I need to search for a record but I dont know the table name or field
It's a joke ?
Please, read carefully this: FAQ181-2886

 

[missinglinq]

Another case for the new version Redmond is reportedly working on: Access ESP!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[Ed2020]

Well if it is a joke thanks a bunch to the poster for wasting my time writing that code! lol.

Ed Metcalfe.

Please do not feed the trolls.....

 

[TheAceMan1]

How are ya johnny45 . . .

If a form have a look at its [blue]RecordSource[/blue] property!

. . . and do give a serious look at the FAQ provided by [blue]PHV![/blue]

See Ya! . . . . . .

 

[johnny45]

Thank you very much for all the posts.... grin
Well here is the situation, started working with an accounting sofware...and in order to "manipulate" the DB outsite of the application itself one needs to understand how records are written and were....I just though that by being able to search across the DB I could track the transactions so as to know which tables are affected .....so rather than key in the using cumbersome interface I could import my inventory count.

Thank you very much Ed2020 for the code, will give it a try on monday .

 

[TheAceMan1]

johnny45 . . .

Just a side note: To get great answers be sure to have a serious look at FAQ219-2884 or FAQ181-2886. One of them already stated by [blue]PHV![/blue] . . .

See Ya! . . . . . .

 

[JoeAtWork]

...and in order to "manipulate" the DB outsite of the application itself one needs to understand how records are written and were....I just though that by being able to search across the DB I could track the transactions so as to know which tables are affected .....so rather than key in the using cumbersome interface I could import my inventory count.

I would advise against directly inserting into another application's database, especially an accounting system. There's a good chance you will not be able to discover all the business rules of the application. You could end up wrecking the system.
Try to find out if the application has any API's for third party developers instead.

 

[johnny45]

Thanks for the heads up JoeAtWork