Thursday, December 8, 2005

How to extract query text from a Microsoft Access Database

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:

Sub extract_view_sql()
' 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")
f.WriteLine qd.Name
f.WriteLine qd.SQL
Set f = Nothing

MsgBox "Done."

End Sub

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.

No comments:

Post a Comment

I moderate comments blog posts over 14 days old. This keeps a lot of spam away. I generally am all right about moderating. Thanks for understanding.