Create SQL Server Views using Access code by
Juan Soto
07/23/2009
Use the Create View TSQL command to create views from your code
From time to time I have a need to create views on the fly from my Access code into a SQL Server database. There are two steps involved: Create the view and then grant permissions for it. The first step is relatively simple:
Dim strSQL as String
strSQL = "Create View vw_myView as Select * from tblMyTable"
You would then use an ADODB command object to create the view. Your ADODB credentials will of course need to provide you with the priviledges to create the view. I usually store admin credentials in my code using a password, but you can just as easily prompt the user for thier credentials on the fly.
The second step is more combersome, you will need to assign security priveledges to the new view. I used code available on the net to grant the Select permission to the Public group on the server:
GrantPerm "Public", "vw_myView", "SELECT"
If you need more details please use the contact form on our site.
