Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / October 2007

Tip: Looking for answers? Try searching our database.

view not in CurrentData.AllViews

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Allen_N - 02 Oct 2007 05:17 GMT
I'm trying to use the following code to determine whether I need to drop an
old view prior to creating a new one. Can anyone tell me why it fails?

   Application.RefreshDatabaseWindow
   DoEvents
   Exists = False
   For Each obj In CurrentData.AllViews
       If obj.Name = strObjectName Then
           Exists = True
           Exit Function
       End If
   Next obj

This routine may return Exists = False even when the view has actually been
created by earlier code.

The first two lines do not always work. Somethimes, I have to press F5 to
make the old view appear in the Database Window, before the routine can
detect it (which tells the VBA code that called it to drop the view).
Sometimes, I can repeat the execution of these two lines in Break Mode, and
the routine will work.

I'm now having to resort to error-trapping, which seems heavy-handed to me.
Pieter Wijnen - 02 Oct 2007 09:51 GMT
Use
SELECT * FROM INFORMATION_SCHEMA.VIEWS

HtH

Pieter

> I'm trying to use the following code to determine whether I need to drop
> an
[quoted text clipped - 23 lines]
> I'm now having to resort to error-trapping, which seems heavy-handed to
> me.
Allen_N - 03 Oct 2007 00:21 GMT
Thanks, Peter. I knew there was something like that.

But, for future reference, can you tell me why

   Application.RefreshDatabaseWindow
   DoEvents

isn't sufficient? Would it have worked if I'd invoked a dummy query on the
view before I tested for its existence?

-- Allen

> Use
> SELECT * FROM INFORMATION_SCHEMA.VIEWS
[quoted text clipped - 30 lines]
> > I'm now having to resort to error-trapping, which seems heavy-handed to
> > me.
Pieter Wijnen - 05 Oct 2007 18:24 GMT
Don't work with ADP's myself.
however: The Beuty of the Information_Schema views are that they are
cross-platform
MSSQL, Oracle MySQL, ...

A shame they don't cover indexes though

Pieter

> Thanks, Peter. I knew there was something like that.
>
[quoted text clipped - 44 lines]
>> > I'm now having to resort to error-trapping, which seems heavy-handed to
>> > me.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.