amr osama amr osama - 1 year ago 59
SQL Question

sql statement to return all fields from all tables

select * from *

yes this is what I want I want to return all fields from all table in my ACCESS database regardless the fields names or the tables names!!!

for example if I have table1 and table2 as tables in my database access file
what I want is to generate this statement

select * from table1,table2

from sql query which run fine in access query

but again I don't know the name of the tables which in the access file.

is there a way for that?

Answer Source

This query will list all the table names

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=6))

You can also use this bit of code to go through every table and list every field

Public Sub List_fields_in_tables()
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

For Each tdf In CurrentDb.TableDefs
    For Each fld In tdf.Fields
        Debug.Print "Table='" & & "'  Field='" & & "'"

    Next fld

Next tdf

End Sub

Hope this helps

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download