Table Location Question

M

metaltecks

I currently have a database that contains about 300 linked tables via ODBC.
I have a report that has a distinct field on it, but I have no idea where it
may lie in one of the 300 linked tables.

Is there a way I can search for it without having to actually open all 300
tables?

Thank you
 
S

Steven Chicago,Illinois

Dear "metaltecks",

Cann't you trace it backwards in the report, by looking at the source for
the field in the report? Is is a query that drives the report or a table?

Good Luck,
Steve
 
S

Steven Chicago,Illinois

Dear "metaltecks",

When you say you have nothing else, does that mean you don't have access to
the database itself?
I have a printout of the results of the report, nothing else.
Click to expand...

Because if that is the answer, then you are out of luck.

Steve
 
M

metaltecks

I do have access to all the tables, I just didn't know if going through each
table is the solution.
 
J

Jerry Whittle

If you have access to the Access database, first find the table that is used
to drive the report or query for the report. Next go to Tools, Database
Utilities, Linked Table Manager. Search for it there.
 
M

metaltecks

I'm sorry, i forgot to mention that the only thing I have is a printout of
the report and I need to recreate it. The names are simple enough to guess
where they may be i a table, but there are a couple I have no idea where they
could be. I could select a table, but it wouldn't help me. What I want to
know is if there is a way I can search the database and see if the field lies
in a specific table without having to open 300 tables.
 
U

UpRider

This could be a real problem, but maybe you can get lucky. Apparently, you
want to recreate a report that previously existed in your database that has
been deleted. (No database backup? You could easily import it from there.)
There is a good chance that the report was based on a query (and if the
query has calculated fields, you would search the 300 tables till doomsday
and never find those fields).
Let's assume that the recordsource for the report is a query. The problem
is, which one? Since you already know some of the tables that contain
fields in the report, the query must also include those tables. Using that
information, screen the existing queries to a list that included those
tables. Maybe the query names will help. Then open each of those queries
and see if your unknown fields are created by the query. If so, bingo!

Good Luck. UpRider
 
P

Pieter Wijnen

this might be of help

Sub FindQueryText(Optional ByVal txt As String = "Formularios", Optional
ByVal ReplaceBy As TextBox = VBA.vbNullString)
Dim Db As DAO.Database
Dim Qdef As DAO.QueryDef

On Local Error Resume Next

Set Db = Access.CurrentDb
For Each Qdef In Db.QueryDefs
If VBA.InStr(Qdef.SQL, txt) > 0 Then
Debug.Print Qdef.Name
If VBA.Len(ReplaceBy) > 0 Then
Qdef.SQL = Replace(Qdef.SQL, txt, ReplaceBy)
End If
End If
Qdef.Close
Next
Set Db = Nothing

End Sub

Pieter
 
Top