Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    =========================

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

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

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Hi Charlotte

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

    Thanks, John

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

Posting Permissions

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