[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 182
  • Last Modified:

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.Execute strProc
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?
0
meciab
Asked:
meciab
  • 3
  • 3
  • 2
1 Solution
 
rafranciscoCommented:
Try this one:

Public Function NewQuery(QuerName As String, sqltext As String)
On Error Resume Next

strProc = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" & QuerName & "]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[" & QuerName & "]"
CurrentProject.Connection.Execute strProc

strProc = "Create View " & QuerName & " as " & _
        Left(sqltext, Len(sqltext) - 1)
CurrentProject.Connection.Execute strProc
End Function
0
 
arbertCommented:
Instead of using sysobjects (shame on you) I would just change it to look at the information_schema views instead.
0
 
meciabAuthor Commented:
I don't understand Arbert could you explain more precisely?
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
arbertCommented:
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.
0
 
meciabAuthor Commented:
How to do it?
0
 
rafranciscoCommented:
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.Execute strProc

strProc = "Create View " & QuerName & " as " & _
        Left(sqltext, Len(sqltext) - 1)
CurrentProject.Connection.Execute strProc
End Function
0
 
meciabAuthor Commented:
so double point for you (125x2)
Thanks
0
 
arbertCommented:
"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.....
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now