Descriptions

[Tazcat]

Does anyone know how I might be able to capture the Description of a table or report from the Database window? There doesn't seem to be anything in AccessObjects that refers directly (or even indirectly) to the Description placed in the Database window properties for a form, report, table, etc. Really appreciate the help on this one.

 

[jbpelletier]

try with

Properties("Description").Value

this exemple will show the description of all object (table,form etc) in your db (DataBasePath)

>>
Dim con As Container
Dim doc As Document
Dim DBa As Database

Dim desc as variant
Set DBa = OpenDatabase("ADataBasePath")

For Each con In DBa.Containers
For Each doc In con.Documents
On Error Resume Next 'because if there is no
'properties entered, it
'will cause an error
desc = ""
desc = doc.Properties("Description").Value
if desc <> "" then
msgbox desc
end if
next doc
next con


jb

 

[Tazcat]

thank you jb!

just one more question if I might... how would you limit the container, if all I wanted was say, the Reports collection?

thanks again

 

[jbpelletier]

'instead of for each con use (after the set Dba=...)
Set con = DBa.Containers("Reports")
'then ....
For Each doc In con.Documents

jb

 

[Tazcat]

bless you!

 

[Tazcat]

hey again jb,

can I bug you for another bit of help? I took your sugestions and ran with them, and am now trying to build a variation of what I wrote to get not the Reports collection, but the Queries. This is giving me an error of "Item not found in this collection" for some reason. I looked into doing it with the Tables container, but that gives me tables and queries both, and I need to limit the list to just queries. Any thoughts?

Private Sub UpdateList_Click()
On Error GoTo ClearTable_Err
Dim obj As AccessObject, dbs As Object
Dim rs As DAO.Recordset
Dim con As Container
Dim doc As Document
Dim DBa As Database
Dim strSQL As String
Dim desc As Variant

'set table to open
strSQL = "Select * from QueriesList"

'open current project, open desired table
Set dbs = Application.CurrentProject
Set rs = CurrentDb.OpenRecordset(strSQL)

'select first record in table
rs.MoveFirst

'delete existing records
Do Until rs.EOF
rs.Delete
rs.MoveNext
Loop

'set containers list to read
Set DBa = Application.CurrentDb
Set con = DBa.Containers("Query")

'read selected container objects and add to table
For Each doc In con.Documents
rs.AddNew
rs!QueryName.Value = doc.Properties("Name").Value

On Error Resume Next
desc = ""
desc = doc.Properties("Description").Value
If desc <> "" Then
rs!Description.Value = desc
End If

rs.Update
Next doc

'reload the table, and redisplay on form
rs.Requery
Forms![500-QueriesList].Requery

'close the table
rs.Close

ClearTable_Exit:
Exit Sub

ClearTable_Err:
MsgBox Error$
Resume ClearTable_Exit

End Sub

 

[jbpelletier]

hi, glad to help you again.

well i dont really know how to know if a con is a table or a query.. but i guess that should help you


using ...dba.containers("TABLES")

try :

dim tbl as tabledef 'kinda temp variable

Set tbl = DBa.TableDefs(doc.Name)
on error resume next
if (Err.Number = 0) then 'its a table (no error trying to
' make a table def)
'code
else
'code 'its a query
end if


im not sure of this part but it should work
jb

 

[cgarts]

Stab in the dark,

Ive noticed that in MSysObjects, in one Db of mine, the Queries and tables have very different values when I use this SQL

SELECT MsysObjects.Name, MsysObjects.Id
FROM MsysObjects
WHERE (((MsysObjects.Name) Like "qry*" Or (MsysObjects.Name) Like "tbl*"))
ORDER BY MsysObjects.Id;

In my Db, all table start withTbl and all queries start with Qry.

Maybe someone who knows more about the MSysObjects table might be able to use MsysObjects.Id to create a bulletproof way of identifying which items are tables and which are queries?

C

 

[jbpelletier]

yeah.. if u check there is a TYPE field..
(in msysobjects)
1 = table, 6 = attach table.. 5 = querries

with a dlookup ("[TYPE]","MsysObjects","NAME = '" & strName "'")
u could find if its a query or a table..

but i prefer the way with error=0.. faster with big BD

jb

 

[Tazcat]

I modified the procedure, but now I'm getting an error "item not found in this collection"?


Set con = DBa.Containers("Tables")

'read selected container objects and add to table
For Each doc In con.Documents
'check if its a table
Set tbl = DBa.TableDefs(doc.Name)
On Error Resume Next
If (Err.Number <> 0) Then
rs.AddNew
rs!QueryName.Value = doc.Properties("Name").Value

On Error Resume Next
desc = ""
desc = doc.Properties("Description").Value
If desc <> "" Then
rs!Description.Value = desc
End If

rs.Update
End If
Next doc

 

[jbpelletier]

sry i made an error

Set tbl = DBa.TableDefs(doc.Name)
on error resume next

should be

on error resume next
Set tbl = DBa.TableDefs(doc.Name)


jb