Dave's Technophorical Times

A blog about Microsoft's Technologies!
SharePoint :: MVC :: ASP.NET :: IIS :: SQL Server :: Visual Studio :: MS Access

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.



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



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


The Blogger

Dave Stuart I'm a Developer with a passion for coding. I enjoy the challengers that come with the job! SharePoint is one of my expert areas along with .NET Web Development with MVC and good old MS Access VBA coding. I Blog so that I can remember how I did that way back when; PLUS all this stuff is searchable! I constantly study and run my own business, Dafran Inc. I have passed 22 Microsoft Exams since 1998 when I first jumped on the treadmill of knowledge. I hope that you enjoy this Blog as much as I enjoy updating it. All the very best from Calgary, Alberta, Canada. contact me at linkedin @ dafran.ca

Calendar

<<  September 2017  >>
MoTuWeThFrSaSu
28293031123
45678910
11121314151617
18192021222324
2526272829301
2345678

View posts in large calendar

Sign in