Access Data Projects – how to test all views

I think that I’ve written this code probably 20 times over the years.

Basically, a lot of people used stored procedures for everything.. and I think that it’s more appropriate to use VIEWS for everything, and stored procedures for just SOME things.

This allows me to quickly and easily test all my views.. If I get an error on the view, typically, I adjust the script such that it will leave the view open in design view, etc.

Hope this helps

Aaron Kempf
MCITP: DBA SQL Server

Option Compare Database
Option Explicit

Public Sub TestAllViews()
On Error GoTo errHandler

DoCmd.SetWarnings False

Dim vw As AccessObject

For Each vw In CurrentData.AllViews
DoCmd.OpenView vw.Name, acViewNormal

CloseTable:
DoCmd.Close acServerView, vw.Name, acSaveNo
GoTo NextView
FailTable:
Debug.Print vw.Name

NextView:
Next vw

cleanExit:
Exit Sub
errHandler:
Debug.Print Err.Number & ” – ” & Err.Description
‘MsgBox vw.Name & vbTab & Err.Number & ” – ” & Err.Description, vbOKOnly
Resume FailTable
End Sub

This entry was posted in SQL Server and tagged . Bookmark the permalink.