Hi Folks! 

I have a quick question for you.
In mySQL the command 'describe table; ' will describe the elements of the 
table.
I know how to do this in SQL Server but I need to figure out a similar 
method in MS Access.

Any suggestions?

If you just want to print out the properties of a table, while in the database window go to the menu options and choose TOOLS
==(then)==> ANALYZE ==(then)==> DOCUMENTER
 Cormac O'Keeffe <9923837@s...> wrote:Hi Folks! 

I have a quick question for you.
In mySQL the command 'describe table; ' will describe the elements of the 
table.
I know how to do this in SQL Server but I need to figure out a similar 
method in MS Access.

Any suggestions?



Lonnie Johnson
ProDev, Professional Development of MS Access Databases
http://www.prodev.us
Let me build your next MS Access database application. 




 



---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now

I am actually developing a Coldfusion based application for a final year 
project in university.

I need a method/query that will return a list of tables in a particular 
datasource (DSN) and another query that will list the contents of a 
selected table.

Have it working in mySQL and SQL Server. Need to get a solution for MS 
Access

> 
If you just want to print out the properties of a table, while in the 
database window go to the menu options and choose TOOLS ==(then)==> 
ANALYZE ==(then)==> DOCUMENTER
 Cormac O'Keeffe <9923837@s...> wrote:Hi Folks! 

I have a quick question for you.
In mySQL the command 'describe table; ' will describe the elements of the 
table.
I know how to do this in SQL Server but I need to figure out a similar 
method in MS Access.

Any suggestions?



Lonnie Johnson
ProDev, Professional Development of MS Access Databases
http://www.prodev.us
Let me build your next MS Access database application. 




 



---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now

Ok. The following query will return the tables in an access database. 
when MSysObjects.Type=1 the MSysObjects.Name returns the name of a table. 


SELECT  MSysObjects.Name  FROM MSysObjects WHERE  MSysObjects.Type=1  AND  
MSysObjects.Name <>  'MSysObjects' AND MSysObjects.Name <>  'MSysQueries'  
AND MSysObjects.Name <>  'MSysAccessObjects'  AND  MSysObjects.Name 
<>  'MSysACEs'  AND  MSysObjects.Name <>  'MSysRelationships'  ;


All i need to do know if figure out how to list the column names and types 
of each table. 

Cormac

Cormac, here's code I used in VB6 to document the tables and queries in a
database:
It "should" work in Access VBA, but I've had mixed results in transferring
code between VB and VBA.

Mark


Dim db          As Database
Dim tblDef      As TableDef
Dim fld         As Field
Dim fieldCount  As Integer
Dim qryDef      As QueryDef


Private Sub cmdGetDBStructure_Click()

Set db = OpenDatabase("MyFilename.mdb")
Open "MyFieldList.txt" For Output As #1

For Each tblDef In db.TableDefs
    Print #1, tblDef.Name & vbTab & "-" & tblDef.Fields.Count
    fieldCount = tblDef.Fields.Count

    'tblDef.Properties
    For i = 0 To fieldCount - 1  'I tried  "For Each fld In tblDef.Fields"
but this didn't work in VB
        Print #1, tblDef.Fields(i).Name
        '-OR- Print #1, ">" & i & vbTab & tblDef.Fields(i).Name '& " " &
tblDef.Fields(i).Properties.Count '& " " & tblDef.Fields(i).Description
    Next
Next
Close
End Sub


Private Sub cmdQueries_Click()

Set db = OpenDatabase("MyFilename.mdb")
Open "MyFieldList.txt" For Output As #1

For Each qryDef In db.QueryDefs
    Print #1, qryDef.Name & vbNewLine & "-" & qryDef.SQL & vbNewLine
Next
Close
End Sub

----- Original Message -----
From: "Cormac O'Keeffe" <9923837@s...>
To: "Access" <access@p...>
Sent: Wednesday, January 29, 2003 9:39 AM
Subject: [access] Re: Ms Access Vs MySQL


> Ok. The following query will return the tables in an access database.
> when MSysObjects.Type=1 the MSysObjects.Name returns the name of a table.
>
>
> SELECT  MSysObjects.Name  FROM MSysObjects WHERE  MSysObjects.Type=1  AND
> MSysObjects.Name <>  'MSysObjects' AND MSysObjects.Name <>  'MSysQueries'
> AND MSysObjects.Name <>  'MSysAccessObjects'  AND  MSysObjects.Name
> <>  'MSysACEs'  AND  MSysObjects.Name <>  'MSysRelationships'  ;
>
>
> All i need to do know if figure out how to list the column names and types
> of each table.
>
> Cormac