Re: Programmatically changing a SQL view in a ADP

Hi Brendan,

Thanks for the code! I'm coming from programming in DAO where you could
change the querydef easily, and I guess it just surprises me that you can't
do it in ADO. Instead you need pass a SQL statement that does it for you.
And maybe what surprises me the most is that this route is not in any of the
MS articles (i.e. that i could find) and that the one article that I did find
recommended a method that does not even work.
I see your way is very similar to docmd.runsql in that you are still passing
a SQL string to the SQL Server to execute on VBA's behalf. I was just
curious if there was a direct method from within VBA to do it.
Thanks!
--
Mav


"Brendan Reynolds" wrote:


I'm not sure whether your objection to the DoCmd.RunSQL "ALTER VIEW ..."
solution is to the use of DoCmd.RunSQL, or to the use of DDL ("ALTER VIEW
...."). If it is the use of DoCmd.RunSQL that you don't like, then the
following might help. If it is the use of DDL that you don't like, then I'm
afraid I don't know what to suggest. I know the use of DDL is not very
friendly, but then I never found ADOX particularly friendly, either. Anyhow,
here's a demo showing how to use DDL via the ADO Connection.Execute method.
In this example I'm creating a new connection, not using
CurrentProject.Connection. You *might* be able to do the same thing using
CurrentProject.Connection. I haven't tested that.

Public Sub TestCreateView()

Const strcConnect As String = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=Northwind;" & _
"Data Source=(local)"

Const strcSQL = "CREATE VIEW TestView AS " & _
"SELECT * FROM Categories"

Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cn = New ADODB.Connection
cn.ConnectionString = strcConnect
cn.Open
cn.Execute strcSQL, , adCmdText
Set rst = New ADODB.Recordset
rst.Open "TestView", cn
Debug.Print rst.Fields(0).Value
rst.Close
cn.Close

End Sub

Public Sub TestAlterView()

Const strcConnect As String = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=Northwind;" & _
"Data Source=(local)"

Const strcSQL = "ALTER VIEW TestView AS " & _
"SELECT * FROM Categories WHERE CategoryID <> 1"

Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cn = New ADODB.Connection
cn.ConnectionString = strcConnect
cn.Open
cn.Execute strcSQL, , adCmdText
Set rst = New ADODB.Recordset
rst.Open "TestView", cn
Debug.Print rst.Fields(0).Value
rst.Close
cn.Close

End Sub

--
Brendan Reynolds
Access MVP

"Mav" <Mav@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2977B340-B433-4C6F-AF91-93813565E35A@xxxxxxxxxxxxxxxx
Greetings!
I have searched the net everywhere, and I can't find a good solution on
how
to alter the 'Select' statement of a SQL Server view from within a ADP
project using VBA. Strangely enough some tutorials expound using ADOX's
catalogue object linked to "CurrentProject.Connection". But this gives
errors when on the next line you try and set a command to a SQL View. The
error is: "Object or provider is not capable of performing requested
operation". Then I checked under MS and they say that you cannot use ADOX
views collection with SQL Server (KB292791).
This whole things does not make sense: why do some tutotrials and
newsgroups
say youu should use the currentconnection when MS says you can't. Am I
missing something?
Another newsgroup said you should try creating a new connection and that
currentproject.connection is the problem. tried that too, same problem.
The
only thing that worked was docmd.runsql "ALTER VIEW....". But is that the
ONLY way to change the querie's SELCT statement.
PS: followiing the VBA code that does not work!
Dim cat As New ADOX.Catalog
Dim cmd As ADODB.Command
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("qryStaffListQuery").Command
cmd.CommandText = strSQL
Set cat.Views("qryStaffListQuery").Command = cmd
Set cat = Nothing

--
Mav



.