Refreshing views/procedures list
Hi
I have a vb function who create view
Public Function NewQuery(QuerName As String, sqltext As String)
On Error Resume Next
DoCmd.DeleteObject acServerView, QuerName
On Error GoTo 0 'Delete if exists
strProc = "Create View " & QuerName & " as " & _
Left(sqltext, Len(sqltext) - 1)
CurrentProject.Connection.
End Function
But when the view is first created, second time that I recreate with the same name I have a "There's an object already named...."
The DoCmd.DeleteObject don't delete the view.
There's a kind of refresh needed.
Someanoe know how to refresh the list?
I have a vb function who create view
Public Function NewQuery(QuerName As String, sqltext As String)
On Error Resume Next
DoCmd.DeleteObject acServerView, QuerName
On Error GoTo 0 'Delete if exists
strProc = "Create View " & QuerName & " as " & _
Left(sqltext, Len(sqltext) - 1)
CurrentProject.Connection.
End Function
But when the view is first created, second time that I recreate with the same name I have a "There's an object already named...."
The DoCmd.DeleteObject don't delete the view.
There's a kind of refresh needed.
Someanoe know how to refresh the list?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Instead of using sysobjects (shame on you) I would just change it to look at the information_schema views instead.
ASKER
I don't understand Arbert could you explain more precisely?
using ths system tables is a bad idea because they can change in future releases (even service packs) of sql server....using Information_schema views protects you a little bit more and is the recommended way to query meta-data.
ASKER
How to do it?
I simply used the same scripts that Enterprise Manager generates when checking for the existence of an object.
To comply with arbert's suggestion, here's how it will look like:
Public Function NewQuery(QuerName As String, sqltext As String)
On Error Resume Next
strProc = "if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = '" & QuerName & "' AND TABLE_TYPE = 'VIEW'
drop view [dbo].[" & QuerName & "]"
CurrentProject.Connection.
strProc = "Create View " & QuerName & " as " & _
Left(sqltext, Len(sqltext) - 1)
CurrentProject.Connection.
End Function
To comply with arbert's suggestion, here's how it will look like:
Public Function NewQuery(QuerName As String, sqltext As String)
On Error Resume Next
strProc = "if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = '" & QuerName & "' AND TABLE_TYPE = 'VIEW'
drop view [dbo].[" & QuerName & "]"
CurrentProject.Connection.
strProc = "Create View " & QuerName & " as " & _
Left(sqltext, Len(sqltext) - 1)
CurrentProject.Connection.
End Function
ASKER
so double point for you (125x2)
Thanks
Thanks
"I simply used the same scripts that Enterprise Manager generates when checking for the existence of an object."
We know how dangerous that can be.....
We know how dangerous that can be.....