Access: How to find a field in a whole database ?

Question

Hi,

I have a database with many tables. I am looking for a field which may or may not exist in one or many of the tables. How do i check if it exists or not. (without querying each table of course). It is an access database

Thanks tksy

Answer 1

MSSQL Looking for ADDRESS1 column: select so.name from sysobjects so where so.id in (select sc.id from syscolumns sc where name like 'ADDRESS1')

ORACLE http://en.wikipedia.org/wiki/Oracle_metadata

Google will find the syntax for other DBs...

Answer 2

What type of database is it? If it's SQL Server you can try this:

SELECT * FROM sysobjects WHERE xtype = 'U' AND name = 'myTable'

But since it's the column you're looking for and not a table (thanks Brian), try this:

SELECT 
    DISTINCT
    so.[name] AS 'Table',
    sc.[name] AS 'Column' 
    FROM 
     syscolumns sc
    JOIN
     sysobjects so
     ON
      so.id = sc.id
    WHERE 
     sc.[name] = 'myTable'

Answer 3

Here's how you access the table schema in MS Access using VBScript:

TestData = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=c:\somefolder\YOURDB.mdb"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open TestData

Set rs = Conn.OpenSchema(4)

do until Rs.eof
    tn = RS("TABLE_NAME")
    fn = RS("COLUMN_NAME")
    ' your script goes here
loop

Answer 4

There is a schema for fields:

Set cn = CurrentProject.Connection

SelectFieldName = "SomeFieldName" 

'Get names of all tables that have a column = SelectFieldName '
Set rs = cn.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, Empty, SelectFieldName))

From: http://stackoverflow.com/questions/277340/ms-access-how-to-bypasssuppress-an-error#277612

Answer 5

Here's what I would do if I wanted to see if a particular column (identified in strSearch) in a particular table.

Public Sub search()

Dim db As Database
Dim strSearch As String
Dim strSQL As String
Dim rsResults As Recordset
Dim i As Integer
Dim cols As Integer

    strSearch = "a3"

    Set db = CurrentDb
    strSQL = "select * from bar"
    Set rsResults = db.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
    If Not rsResults.BOF Then
        rsResults.MoveFirst
    End If

    cols = rsResults.Fields.Count - 1 ' -1 because we start counting cols at 0
    For i = 0 To cols
        If rsResults(i).Name = strSearch Then
            MsgBox "Found the seach string"
        End If
    Next
    MsgBox "end of script"

End Sub

Now I know you don't want to write one of those for each table. So the next thing to do would be to loop through all the tables. You can find a list of all the tables with the following SQL

SELECT 
    name
FROM 
    MSysObjects 
WHERE 
    (Left([Name],1)<>"~") 
    AND (Left([Name],4) <> "MSys") 
    AND ([Type] In (1, 4, 6))

Connecting these two pieces up together, I'll leave as an exercise for the student :)

Answer 6

If you really do not want to open any table, a solution is to use the tabledefs collection of the database object. Each tabledef item has its own fields collection that you can browse. It would give something like that:

Public function findFieldInDatabase(p_myFieldName)
dim db as database, _
    tb as tabledef, _
    fd as field

set db = currentDb
for each tb in db.tabledefs
    for each fd in tb.fields
        if fd.name = p_myFieldName then
            debug.print tb.name, fd.name
        end if
    next fd
next tb
set fd = nothing
set tb = nothing
set db = nothing
end function

This code could be easily adapted to accept an optional p_myTableName as an argument to limit the search to a table/range of tables.