I wish to look for a particular area, available in tables of the Access database. Is there's any utility to locate this?
Yes it can be done VBA code. I've e-mailed you.
Public Function FindField(fieldname As String) Dim db As Database Dim td As TableDef Dim fd As Field Set db = DBEngine(0)(0) db.TableDefs.Refresh For Each td In db.TableDefs For Each fd In td.fields If fieldname = fd.Name Then Debug.Print td.Name End If Next Next db.Close End Function
You should use ADO Schemas:
Function ListTablesContainingField(SelectFieldName) As String ''Tables returned will include linked tables Dim cn As New ADODB.Connection Dim rs As ADODB.Recordset Dim strTempList As String On Error GoTo Error_Trap Set cn = CurrentProject.Connection ''Get names of all tables that have a column called <SelectFieldName> Set rs = cn.OpenSchema(adSchemaColumns, _ Array(Empty, Empty, Empty, SelectFieldName)) ''List the tables that have been selected While Not rs.EOF ''Exclude MS system tables If Left(rs!Table_Name, 4) <> "MSys" Then strTempList = strTempList & "," & rs!Table_Name End If rs.MoveNext Wend ListTablesContainingField = Mid(strTempList, 2) Exit_Here: rs.Close Set cn = Nothing Exit Function Error_Trap: MsgBox Err.Description Resume Exit_Here End Function
See also: http://support.microsoft.com/kb/186246
I perform a large amount of maintenance and integration operate in access along with a vba module compiled by Allen Browne totally rocks.
Inside your immediate window type
It'll search your database (tables, queries, forms, reviews) to locate in which a particular area title can be used.
Documentation and code is here now http://allenbrowne.com/ser-73.html