Microsoft Access 2007-2010: Removing a Control Layout

Do you use the report wizard to help you create reports in Microsoft Access 2007? If you do, you are probably aware of the control layout. Many controls are automatically grouped into a layout when you use the wizard to create a report. These control layouts help you to arrange the controls, i.e. text boxes and labels, as a group. When you move or resize one control, the other controls will automatically adjust accordingly. While using layouts can save you time when manipulating controls, they can also be restricting. It is possible to remove a control layout. To remove a control layout, you need to view the report in the design or layout view. Once in the appropriate view, select the controls you want to remove from the layout. Then select the Arrange tab on the ribbon and click the Remove button located within the control layout section. With the control layout removed you will be able to independently move and resize the controls within your report.

MSAccess (2003) SQL Trace (TraceSQLMode)

If you want to switch on the Trace log for MSAccess linked tables to SQL Server then set the following registry key to 1. If you can't see the key then create a new REG_DWORD key and set the value to 1. The log file will be called sqlout.txt and it will be produced in the Default database folder as indicated in MSAccess Tools->Options->General->Default database folder.

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC] TraceSQLMode

Hope this helps! Also don't forget to switch it off when you don't need it.

Access ADP Tables and Fields

This is how to list all Tables and Fields in an Access Database Project (ADP).

Option Compare Database
Option Explicit

Public Sub ListTablesAndFieldsADP()
    '---------------------------------------------------------------------
    'Reference is needed for: Microsoft ADO Ext. 2.x for DDL and Security
    '---------------------------------------------------------------------
    Dim cat As New ADOX.Catalog 
    Dim tbl As ADOX.Table       
    Dim col As ADOX.Column       
    Dim fs As Object
    Dim output As Object

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set output = fs.CreateTextFile("c:\Tables_Views_And_Fields.txt", True)
    
    Set cat.ActiveConnection = CurrentProject.Connection
    
    For Each tbl In cat.Tables
        If Left(tbl.Type, 6) <> "SYSTEM" Then
            output.WriteLine tbl.Name & " (" & tbl.Type & ")"
            For Each col In tbl.Columns
                output.WriteLine "  " & col.Name
            Next
            output.WriteLine
        End If
    Next
    
    Set col = Nothing
    Set tbl = Nothing
    Set cat = Nothing
End Sub

MS Access Database Tables and Fields

This is how to list all LINKED Tables and Fields in an MS Access Database.
To list Local Tables, remove Len(tdfCurrent.Connect) > 0

Public Sub ListTablesAndFields()
    Dim fld As DAO.Field
    Dim tdfCurrent As DAO.TableDef
    Dim dbCurrent As DAO.Database
    
    Set dbCurrent = CurrentDb
    
    Dim fs As Object
    Dim output As Object
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set output = fs.CreateTextFile("c:\Tables_Views_And_Fields.txt", True)
    
    For Each tdfCurrent In dbCurrent.TableDefs
        If Len(tdfCurrent.Connect) > 0 And Left(tdfCurrent.Name, 4) <> "~TMP" Then
            output.WriteLine tdfCurrent.Name
            For Each fld In tdfCurrent.Fields
                output.WriteLine "   " & fld.Name
            Next
            output.WriteLine
        End If
    Next
End Sub