Programmatically changing a SQL view in a ADP

G

Guest

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
 
B

Brendan Reynolds

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
 
G

Guest

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!
 
B

Brendan Reynolds

One of the differences between DAO and ADO is that DAO combines data
manipulation and data definition functions in one library. ADO attempts to
separate them. The idea, I believe, was to make more efficient use of
resources by allowing you to load only the libraries you actually needed.

I never really got very familiar with ADOX, so I can't say for sure that
there isn't a way to do this successfully with ADOX. But T-SQL, SQL Server's
dialect of SQL, has extremely powerful data definition features, so you can
probably do just about anything you could do via ADOX by executing T-SQL
statements instead. This has the advantage that you no longer need a
reference to the ADOX object library, which is one less thing to potentially
go wrong when you install your app on another PC.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top