Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
Orders
OrderID dbLong PrimaryKey Indexed
CustomerID dbText ForiegnKey Indexed
EmployeeID dbLong ForiegnKey Indexed
OrderDate dbDate Indexed
RequiredDate dbDate
ShippedDate dbDate Indexed
ShipVia dbLong ForiegnKey Indexed
Freight dbCurrency
ShipName dbText
ShipAddress dbText
ShipCity dbText
ShipRegion dbText
ShipPostalCode dbText Indexed
ShipCountry dbText
Name: CustomersOrders
Table: Customers
Foreign Table: Orders
PK: CustomerID FK:CustomerID
Name: EmployeesOrders
Table: Employees
Foreign Table: Orders
PK: EmployeeID FK:EmployeeID
Name: OrdersOrder Details
Table: Orders
Foreign Table: Order Details
PK: OrderID FK:OrderID
Name: ProductsOrder Details
Table: Products
Foreign Table: Order Details
PK: ProductID FK:ProductID
Name: ShippersOrders
Table: Shippers
Foreign Table: Orders
PK: ShipperID FK:ShipVia
Sales Totals by Amount
SaleAmount dbCurrency
OrderID dbLong
CompanyName dbText
ShippedDate dbDate
SELECT DISTINCTROW [Order Subtotals].Subtotal AS SaleAmount, Orders.OrderID, Customers.CompanyName, Orders.ShippedDate
FROM Customers INNER JOIN (Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID) ON Customers.CustomerID = Orders.CustomerID
WHERE ((([Order Subtotals].Subtotal)>2500) AND ((Orders.ShippedDate) Between #1/1/95# And #12/31/95#));
'Module Name: Tek Tips Documenter
'Developed by: MajP
'Special Thanks: Skip Vought for help with query 'documentation
'
'Purpose: This Module provides some utilities to help document your database so you can
'better explain it to the Tek Tips community. Often to get help from Tek Tips you need
'to explain your tables, fields, and relationships. This can be time consuming. These
'procedures simplify that process
'Procedures:
'1. DocumentTables: This procedure document all tables and field within each table. It lists
' the field type, primary keys, foreign keys, and indices.
'2. DocumentRelations: This procedure documents all relationships in the database and list the
' table, foreign table, primary keys, and foreign keys.
'How to use:
'1. Place this code in a STANDARD MODULE
'2. Run the procedures from the immediate window.
'3. Post the results in Tek Tips
'
'************************ Class Code Start ****************************************************
Option Compare Database
Option Explicit
Public Function fncDocumentTables() As String
Dim strDocument As String
Dim tblDef As DAO.TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index
For Each tblDef In CurrentDb.TableDefs
If Not Left(tblDef.Name, 4) = "MSys" Then
strDocument = strDocument & vbCrLf & tblDef.Name & vbCrLf
For Each fld In tblDef.Fields
strDocument = strDocument & " " & fld.Name & " " & fncFldTypeToString(fld.Type)
If isPK(tblDef, fld.Name) Then
strDocument = strDocument & " PrimaryKey"
End If
If isFK(tblDef, fld.Name) Then
strDocument = strDocument & " ForiegnKey"
End If
If isIndex(tblDef, fld.Name) Then
strDocument = strDocument & " Indexed"
End If
If fld.Required Then
strDocument = strDocument & " Required"
End If
strDocument = strDocument & vbCrLf
Next fld
End If
Next tblDef
fncDocumentTables = strDocument
End Function
Public Function fncFldTypeToString(intFieldType As Integer) As String
Select Case intFieldType
Case 1
fncFldTypeToString = "dbBoolean"
Case 2
fncFldTypeToString = "dbByte"
Case 3
fncFldTypeToString = "dbInteger"
Case 4
fncFldTypeToString = "dbLong"
Case 5
fncFldTypeToString = "dbCurrency"
Case 6
fncFldTypeToString = "dbSingle"
Case 7
fncFldTypeToString = "dbDouble"
Case 8
fncFldTypeToString = "dbDate"
Case 9
fncFldTypeToString = "dbBinary"
Case 10
fncFldTypeToString = "dbText"
Case 11
fncFldTypeToString = "dbLongBinary"
Case 12
fncFldTypeToString = "dbMemo"
Case 13
fncFldTypeToString = "Text"
Case 14
fncFldTypeToString = "Text"
Case 15
fncFldTypeToString = "dbGUID"
Case 16
fncFldTypeToString = "dbBigInt"
Case 17
fncFldTypeToString = "dbVarBinary"
Case 18
fncFldTypeToString = "dbChar"
Case 19
fncFldTypeToString = "dbNumeric"
Case 20
fncFldTypeToString = "dbDecimal"
Case 21
fncFldTypeToString = "dbFloat"
Case 22
fncFldTypeToString = "dbTime"
Case 23
fncFldTypeToString = "dbTimeStamp"
End Select
End Function
Public Function isPK(tblDef As DAO.TableDef, strField As String) As Boolean
Dim idx As DAO.Index
Dim fld As DAO.Field
For Each idx In tblDef.Indexes
If idx.Primary Then
For Each fld In idx.Fields
If strField = fld.Name Then
isPK = True
Exit Function
End If
Next fld
End If
Next idx
End Function
Public Function isIndex(tblDef As DAO.TableDef, strField As String) As Boolean
Dim idx As DAO.Index
Dim fld As DAO.Field
For Each idx In tblDef.Indexes
For Each fld In idx.Fields
If strField = fld.Name Then
isIndex = True
Exit Function
End If
Next fld
Next idx
End Function
Public Function isFK(tblDef As DAO.TableDef, strField As String) As Boolean
Dim idx As DAO.Index
Dim fld As DAO.Field
For Each idx In tblDef.Indexes
If idx.Foreign Then
For Each fld In idx.Fields
If strField = fld.Name Then
isFK = True
Exit Function
End If
Next fld
End If
Next idx
End Function
Public Function fncDocumentRelations() As String
Dim strDocument As String
Dim rel As DAO.Relation
Dim fld As DAO.Field
Dim idx As DAO.Index
Dim prop As DAO.Property
For Each rel In CurrentDb.Relations
strDocument = strDocument & vbCrLf & "Name: " & rel.Name & vbCrLf
strDocument = strDocument & " " & "Table: " & rel.Table & vbCrLf
strDocument = strDocument & " " & "Foreign Table: " & rel.ForeignTable & vbCrLf
For Each fld In rel.Fields
strDocument = strDocument & " PK: " & fld.Name & " FK:" & fld.ForeignName
strDocument = strDocument & vbCrLf
Next fld
Next rel
fncDocumentRelations = strDocument
End Function
Public Function fncDocumentQueries() As String
Dim strDocument As String
Dim qryDef As DAO.QueryDef
Dim fld As DAO.Field
Dim idx As DAO.Index
For Each qryDef In CurrentDb.QueryDefs
If Not (Left(qryDef.Name, 4) = "MSys" Or Left(qryDef.Name, 4) = "~sq_") Then
strDocument = strDocument & vbCrLf & qryDef.Name & vbCrLf
For Each fld In qryDef.Fields
strDocument = strDocument & " " & fld.Name & " " & fncFldTypeToString(fld.Type)
strDocument = strDocument & vbCrLf
Next fld
strDocument = strDocument & qryDef.SQL & vbCrLf
End If
Next qryDef
fncDocumentQueries = strDocument
End Function
Public Sub DocumentTables()
Debug.Print fncDocumentTables
End Sub
Public Sub DocumentRelations()
Debug.Print fncDocumentRelations
End Sub
Public Sub DocumentQueries()
Debug.Print fncDocumentQueries
End Sub