April 24, 2008 at 4:05 pm
I need to see the tables that Reports and Forms use in Access. When I used Documenter, I do not see the table identified. I see they use the Control property, but it does say if its a column or a table. Any suggestions?
April 24, 2008 at 4:14 pm
I gess that you want to see what is the source of the report...
Goto the report, then under properties, find "Record Source" , it could be a table or a query
Regards
April 25, 2008 at 9:04 am
Thanks for your feedback.
It shows the query. But I need the underlying table name.
As far as I can tell, the only advanatage of the Documenter is it revealing Tables. It doesn't show the other objects.
I can't deal with incomplete documentation from MS! :w00t:
April 30, 2008 at 11:26 am
The documenter will give the record source of your reports...
If the Recod source is a query then you have to see the query definition that is pointing at.
I am curious of what type of documentation you will like to see...
April 30, 2008 at 1:51 pm
Jorge.G (4/30/2008)
The documenter will give the record source of your reports...If the Recod source is a query then you have to see the query definition that is pointing at.
I am curious of what type of documentation you will like to see...
Jorge, thanks for your reply. I need to see the underlying tables to Reports and Forms.
The Documenter when run on a Report, for example, doesn't indicate whether the object is query or a table. It merely says "RecordSource: ". Whether the object is a Query or a Table, I have no idea, I have to look it up in the DB.
I have over 500 reports and forms I have to go to determine where they are getting their data. With Tables and Queries, the Documenter was EXCELLENT! I pulled this infrom them in no time. But with Reports and Forms, I have to go through each one through Object Dependencies.
So whether the object is a Report or a Form, Documenter seems to be useless, although the documentation I have found doesn't seem to identify this deficiency.
Any suggestions
April 30, 2008 at 3:54 pm
I see...You are right then, the documenter will not show you whether is a query or a table, may be this is the reason that seasoned developers add prefix "tbl" to tables and "qry" to queries... 😉
To look for dependencies
Take a look at the following link
http://office.microsoft.com/en-us/access/HP063626521033.aspx
It may help you finding dependencies but will not create a tree nor an advanced documentation.
May 1, 2008 at 7:27 am
Jorge.G (4/30/2008)
I see...You are right then, the documenter will not show you whether is a query or a table, may be this is the reason that seasoned developers add prefix "tbl" to tables and "qry" to queries... 😉
nice observation because that naming convention makes sense in the context of the dialog boxes and other GUI boxes (as well as the Documenter report) that are accessed. Interestingly enough, that convention becomes even more important to "expose" the underlying source of the object when introducing GUI approaches to managing and buidling tables and queries. (Most of the tables I have to work with are merely constructs of underlying queries).
many thanks to you, Jorge, and to the wealth of contributions in this forumn. 😀
May 1, 2008 at 10:19 am
You could loop through all the objects and build a table with that info. Here is an example how to do the forms; the reports would be similar:
Sub GetDataSources_Forms()
Const MyTable = "MyDataSources_Forms"
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As DAO.TableDef
Dim qdf As DAO.QueryDef
Dim fld As DAO.field
Dim doc As Document
Dim frm As Form
Dim ds As String
'
On Error Resume Next
DoCmd.DeleteObject acTable, MyTable
'
Set db = CurrentDb
' create table to store the results
Set tdf = db.CreateTableDef(MyTable)
With tdf
Set fld = tdf.CreateField("FormName", dbText, 64)
tdf.Fields.Append fld
Set fld = tdf.CreateField("SourceType", dbText, 1)
tdf.Fields.Append fld
Set fld = tdf.CreateField("SourceName", dbText, 64)
tdf.Fields.Append fld
Set fld = tdf.CreateField("SourceSQL", dbMemo)
tdf.Fields.Append fld
End With
db.TableDefs.Append tdf
Set fld = Nothing
Set tdf = Nothing
'
Set rs = db.OpenRecordset(MyTable)
On Error Resume Next
For Each doc In db.Containers("Forms").Documents
rs.AddNew
rs!FormName = doc.Name
DoCmd.OpenForm doc.Name, acDesign
Set frm = Forms(doc.Name)
ds = frm.RecordSource
If ds <> "" Then
rs!SourceName = ds
Set tdf = CurrentDb.TableDefs(ds)
If Err.Number = 0 Then
rs!SourceType = "T"
Else
rs!SourceType = "Q"
Err.Clear
Set qdf = db.QueryDefs(ds)
If Err.Number = 0 Then
rs!SourceSQL = qdf.sql
Else
rs!SourceSQL = ds
Err.Clear
End If
End If
End If
'
Set tdf = Nothing
Set qdf = Nothing
DoCmd.Close acForm, doc.Name, acSaveNo
rs.Update
Next doc
Set db = Nothing
'
End Sub
May 1, 2008 at 11:27 am
Mitchell,
Absolutely positive reply. I appreciate you help!
2 questions:
1: Is this VBA Code? (sorry, not a programmer per se)
2: Where is the code run? on the CL? Where do I execute it? In what application?
Many thanks
May 1, 2008 at 11:45 am
Yes, this is VBA. Create a new standard module in Access:
From the database window, select Modules and then click New
And then paste this code in, and place your cursor anywhere in the area between the Sub and End Sub, and then press F5 to run the code. The screen will flash repeatedly as Access opens each form in design view, reads the info, and then closes it. When the flashing stops, you'll have a new table with your info.
May 1, 2008 at 5:20 pm
I posted the code at my web site, the Forms version (shown above) is at
http://www.wvmitchell.com/tips/GetDataSources_Forms.htm
and the Reports version is at
November 7, 2008 at 8:31 am
Perfect job William. Thank you also from my side!
Can you do the same for Tables proprieties like:
- table name
- field name
- field type
- field dimension
- Key type (unique)
Thank you a lot in advance. Any help wille be always appreciated.
Luigi
PS. I need to upsize to SQL 2000 a 2003 mdb containing 96 tables, 185 queries and more than 100 frm's. The info I can get from a VBA code like yours is 100% uasble during the upsize process.
Thank you again
November 7, 2008 at 9:08 am
The documenter is basically an instance of the old "you get what you pay for". It's free, therefore you get to live with its limitations.
You will get substantially better info from some of the premium documenters. I personally used to like FMS Analyzer a lot (I get enough info out of Visual Studio these days not to need FMS any more.
The link to is is here:
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 7, 2008 at 9:33 am
Here is some documenter code that I wrote about 10 years ago.
http://www.wvmitchell.com/blogger/billdoc_2k.zip
It has one module, three reports and a macro. You would import everything into your mdb and then run the macro.
You'll probably be most interested in the tables wm_TablesFields and wm_QueriesFields.
HTH
November 7, 2008 at 12:55 pm
Matt, William
thank you very for two things:
- the prompt reply, really quick.... and
- the perfect solution (William)
I have just upsized the 2000 tools to 2003, linked the table from my db, and then imported queries, reports and forms...... en voila'. Done
Thank you again... I will come back often here!
PS. Maybe adding also the info over the keys ....... could complete your tool. Anyway it is ok also as it is. 🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply