How to extract the text from MSAccess Views:
I needed to extract the SQL text of a bunch of MSAccess views recently. I was hoping for a system table with this information, like Oracle's DBA_VIEWS. I did not find anything like that, so I wrote this script.
Create a VBA module in the Access project (To do this hit Alt-F11, Right click "Modules", insert-> Module)
You will need to add references to Microsoft DAO and Microsoft Scripting objects (Tools->References) , then add in this text:
' Add two references to the project:
' 1) Microsoft DAO 3.6 Object Library
' 2) Microsoft Scripting Runtime
Const viewdir = "C:\TEMP\"
Dim db As database
Dim qd As querydef
Dim fso As New FileSystemObject
Dim f As TextStream
Set db = CurrentDb()
For Each qd In db.QueryDefs
Set f = fso.CreateTextFile(viewdir & qd.Name & ".sql")
Set f = Nothing
Run this by clicking within the sub text and hitting F5. The text of each query will be stored to a text file.
This felt kludgy, but I have found no other way to get SQL text from Microsoft Access Views.