Can I Print this Table Report? (A2k (9.0.3821) SR-1 )

[Support4John]

Can I Print this Table Report?

A2k (9.0.3821) SR-1

Is there a way to duplicate the following report from (Tools, Analyzer, Documenter, Table) in VBA with the addition of total number fields and total size?

Can you point me to some code?

Thanks, John

=====================================
d:access2kBuild.mdb Tuesday, April 23, 2002
Table: Customers Page: 1

Columns
Name Type Size

CustomerID Text 5
CompanyName Text 40
ContactName Text 30
ContactTitle Text 30
Address Text 60
City Text 15
Region Text 15
PostalCode Text 10
Country Text 15
Phone Text 24
Fax Text 24
Number of fields: 11 -------
Total Size: 268

[Support4John]

Hi All

While wating for a response I worked out the following and it works OK.

Is there any way to sort the field names alphabetically within the following statement?

Set rs = db.OpenRecordset(strTableName, dbOpenSnapshot)

Thanks, John

=====================
Function DisplayTableFieldInformation(strTableName As String)

On Error GoTo ErrorHandling_Err

Dim db As Database
Dim rs As Recordset
Dim FieldName As Field
Dim i As Integer
Dim RetVal As String
Dim Size As Long

DoCmd.Hourglass True
Close #1
Open "TableDef.txt" For Output As #1

Set db = CurrentDb
Set rs = db.OpenRecordset(strTableName, dbOpenSnapshot)

Print #1, "Table Definition Date: " & Now
Print #1, atCNames(1) & " " & atCNames(2) & " " & db.Name
Print #1, "Table Name: " & rs.Name
Print #1, "Field Count: " & rs.Fields.Count
Print #1, ""
Print #1, "Field Name"; Tab(30); "Size"; Tab(40); "Type"
Print #1, "--------------------"; Tab(30); "---- "; Tab(40); "----------"

For i = 0 To rs.Fields.Count - 1
Set FieldName = rs.Fields(i)
Print #1, FieldName.Name; Tab(30); _
FieldName.Size; Tab(40); _
FieldType(FieldName.Type)
Size = Size + FieldName.Size
Next i

Print #1, ""
Print #1, "Total Size:"; Tab(30); Size
Close #1

DoCmd.Hourglass False
db.Close
Set db = Nothing
RetVal = Shell("notepad TableDef.txt", vbNormalFocus)

ErrorHandling_Err:
If Err Then
Exit Function
End If
End Function
=====================
Function FieldType(fieldConstant As Integer) As String

Select Case fieldConstant
Case 1
FieldType = "Boolean"
Case 2
FieldType = "Byte"
Case 3
FieldType = "Integer"
Case 4
FieldType = "Long Integer"
Case 5
FieldType = "Currency"
Case 6
FieldType = "Single"
Case 7
FieldType = "Double"
Case 8
FieldType = "Date"
Case 10
FieldType = "Text"
Case 11
FieldType = "OLE Object"
Case 12
FieldType = "Memo"
Case 15
FieldType = "GUID"
End Select
End Function
=========================

[charlotte]

The easiest way would be to read the information into an array, then sort that before printing the whole thing.

[Support4John]

Hi Charlotte

Any code kicking around that demonstrates loading, sorting, accessing an array?

Thanks, John

[HansV]

If you will excuse me for butting in, below is some code that uses an array. It uses a TableDef instead of a Recordset to get the field names.

Remark:
The sort routine uses a very simple bubble sort. For small arrays like the field names array, that is OK. For large arrays, bubble sort is horribly slow. Execution time increases with the square of the array size. There are far better methods to sort large arrays (Shell sort, QuickSort).

An alternative would be to write the field names to a temporary table and then retrieve them in alphabetic order using a query.

<img src=/w3timages/blueline.gif width=33% height=2>

Sub DisplayTableFieldInformation(strTableName As String)
Dim dbs As Database
Dim tdf As TableDef
Dim fld As Field
Dim arrFields() As String
Dim i As Integer
Dim intFieldCount As Integer

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTableName)
intFieldCount = tdf.Fields.Count
ReDim arrFields(0 To intFieldCount - 1)

For i = 0 To intFieldCount - 1
arrFields(i) = tdf.Fields(i).Name
Next i

SortArray arrFields

For i = 0 To intFieldCount - 1
Set fld = tdf.Fields(arrFields(i))
' Do something with field - here I display some info in the debug window
Debug.Print fld.Name, fld.Type, fld.Size
Next i
End Sub

Sub SortArray(a() As String)
' Simple bubble sort
' Not very efficient, but OK for small arrays
Dim i As Integer
Dim j As Integer
Dim Low As Integer
Dim Hi As Integer
Dim strTemp As String
Dim Swapped As Boolean

Low = LBound(a)
Hi = UBound(a)

For i = Low To Hi - 1
Swapped = False
For j = i + 1 To Hi
If a(i) > a(j) Then
strTemp = a(i)
a(i) = a(j)
a(j) = strTemp
Swapped = True
End If
Next j
If Not Swapped Then Exit Sub
Next i
End Sub

[Support4John]

Hi Hans

Works perfectly.

Thanks to Charlotte for helping me ask the question.

What a great clinic in array and bubble sort technique.

Where do I find more information on Shell sort and QuickSort?

Thanks, John

[Francois]

For Quick sort : KB Q189631
For Shell sort : KB Q169617

[HansV]

If you do a search on "shell sort" or "quicksort" in any decent search engine like Google, you'll find lots of references to algorithms, explanations etc.

Here are possible implementations for sorting integers:

Sub ShellSort(a() As Integer)
' Very fast sort: 2n Log n comparisons
' Calling convention:
' Redim a(1 To 20) as Integer
' ShellSort a()
Dim i As Integer, j As Integer, h As Integer, Low As Integer, Hi As Integer
Dim Temp As Integer

Low = LBound(a)
Hi = UBound(a)

' Compute highest increment
h = 1
Do While h < Hi - Low + 1
h = 3 * h + 1
Loop
h = h 3

Do While h > 0
' Sort by insertion in increments of h
For i = Low + h To Hi
Temp = a(i)
For j = i - h To Low Step -h
If a(j) <= Temp Then Exit For
a(j + h) = a(j)
Next j
a(j + h) = Temp
Next i
h = h 3
Loop
End Sub

Sub QuickSort(a() As Integer, ByVal Low As Integer, ByVal Hi As Integer)
' Very fast sort: n Log n comparisons
' Calling convention:
' Redim a(1 To 20) as Integer
' QuickSort a(), 1, 20
Dim MidValue As Integer, i As Integer, j As Integer, Temp As Integer
If Hi <= Low Then Exit Sub
MidValue = a((Low + Hi) 2)
i = Low
j = Hi
Do While i <= j
If a(i) >= MidValue And a(j) <= MidValue Then
Temp = a(i)
a(i) = a(j)
a(j) = Temp
i = i + 1
j = j - 1
Else
If a(i) < MidValue Then i = i + 1
If a(j) > MidValue Then j = j - 1
End If
Loop
QuickSort a(), Low, j
QuickSort a(), i, Hi
End Sub

If you're going to sort large numbers of strings, you should consider sorting the index numbers of the strings instead of the strings themselves - much more memory efficient. It takes a bit long to explain all that here; you will undoubtedly find information on that when you do a Google search.