report in MS Access

Results 1 to 2 of 2

Thread: report in MS Access

  1. #1
    Join Date
    Dec 1969

    Default report in MS Access

    i have MS Access database in which i have around 100 tables.<BR> <BR>i need a report or a document in which i will get table name, under it all fields with datatype and table description.<BR> <BR>is there any option in MS Access which will help me out to generate this without doing it manually??<BR>its urgent.<BR> <BR>regds,<BR>sud

  2. #2 Guest

    Default RE: report in MS Access

    Well there are two ways:<BR>1) Relationships: (from the menu Tools/Relationships) This brings up the relationships screen, and allows you to select tables to display. You can add all of your tables to the screen. you then may need to resize and move each of the resulting table boxes until you see all the fields. With a little work this will give you a very useful diagram of all tables, and in Access 2000 you can print it out.<BR>2) Documenter: (Tools/Analyze/Documenter) This will let you select any database objects (tables, queries, forms, reports, modules), allowing you to specify the level of details you want, and to printout a report (rather long-winded, but handy!).<BR><BR>Also I have a module in Access VBA which creates 2 tables, "Tables" and "Fields", which summarise some useful properties of your tables and fields. (I then create a master/detail form, with [Tables] as the main form and [Fields] in the subfrom. This can then be printed as a form of database documentation.) Here is the code, just create a new module, paste it in and run it:<BR><BR>Option Compare Database<BR><BR>Sub ListTablesandFields()<BR> Dim tbl As DAO.TableDef, fld As DAO.Field, Q As String<BR> Dim RsT As DAO.Recordset, RsF As DAO.Recordset, P As DAO.Property<BR> <BR> <BR> &#039; DoCmd.RunSQL "Drop table [Tables];"<BR> Q = "Create table [Tables](" & _<BR> "tname text(64)," & _<BR> "[Desc] text(250));"<BR> DoCmd.RunSQL Q<BR> <BR> &#039; DoCmd.RunSQL "Drop table [Fields];"<BR> Q = "Create table [Fields](" & _<BR> "tname text(64)," & _<BR> "fname text(64)," & _<BR> "[Type] text," & _<BR> "[Desc] text(250));"<BR> DoCmd.RunSQL Q<BR> <BR> Set RsT = CurrentDb.OpenRecordset("Tables")<BR> Set RsF = CurrentDb.OpenRecordset("Fields")<BR> <BR> For Each tbl In CurrentDb.TableDefs<BR> If tbl.Name &#060;&#062; "Tables" And tbl.Name &#060;&#062; "Fields" And Left(tbl.Name, 4) &#060;&#062; "MSys" Then<BR> RsT.AddNew<BR> RsT!TName = tbl.Name<BR> RsT!desc = GetDesc(tbl)<BR> RsT.Update<BR> For Each fld In tbl.Fields<BR> RsF.AddNew<BR> RsF!fname = fld.Name<BR> RsF!TName = tbl.Name<BR> RsF!Type = FldType(fld.Type)<BR> RsF!desc = GetDesc(fld)<BR> RsF.Update<BR> Next<BR> End If<BR> Next<BR> RsF.Close<BR> Set RsF = Nothing<BR> RsT.Close<BR> Set RsT = Nothing<BR>End Sub<BR><BR>Function GetDesc(o) As String<BR> GetDesc = ""<BR> For Each P In o.Properties<BR> If P.Name = "Description" Then GetDesc = P.Value<BR> Next<BR>End Function<BR><BR>Function FldType(typ As Integer) As String<BR> Select Case typ<BR> Case dbBigInt<BR> FldType = "Big Integer"<BR> Case dbBinary<BR> FldType = "Binary"<BR> Case dbBoolean<BR> FldType = "Boolean"<BR> Case dbByte<BR> FldType = "Byte"<BR> Case dbChar<BR> FldType = "Char"<BR> Case dbCurrency<BR> FldType = "Currency"<BR> Case dbDate<BR> FldType = "Date / Time"<BR> Case dbDecimal<BR> FldType = "Decimal"<BR> Case dbDouble<BR> FldType = "Double"<BR> Case dbFloat<BR> FldType = "Float"<BR> Case dbGUID<BR> FldType = "Guid"<BR> Case dbInteger<BR> FldType = "Integer"<BR> Case dbLong<BR> FldType = "Long"<BR> Case dbLongBinary<BR> FldType = "Long Binary (OLE Object)"<BR> Case dbMemo<BR> FldType = "Memo"<BR> Case dbNumeric<BR> FldType = "Numeric"<BR> Case dbSingle<BR> FldType = "Single"<BR> Case dbText<BR> FldType = "Text"<BR> Case dbTime<BR> FldType = "Time"<BR> Case dbTimeStamp<BR> FldType = "Time Stamp"<BR> Case dbVarBinary<BR> FldType = "VarBinary"<BR> End Select<BR>End Function<BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts