Post reply

New View not available

  • October 20, 2004 at 3:01 pm

    #161753

    I am converting an Access linked DB to an Access Project. I seem to be having a timing issue.

    I have a form which uses VBA to create a View, then opens it. My problem is it comes back with an error of can't find the object [ViewName]. I know it creates the view, I can see it, but apparently Access Project cannot see it right after it is created.

    My code:

        Set ADOCmd = New ADODB.Command

        Set ADOCmd.ActiveConnection = Dbs

        SQL = "CREATE VIEW " & QueryName(UBound(QueryName)) & " AS " & SQL

        ADOCmd.CommandText = SQL

        Set Rs = ADOCmd.Execute

        DoEvents

        DoCmd.OpenView QueryName(UBound(QueryName)), acViewNormal

    How do I refresh the Project or whatever so I can open the View? DoEvents obviously does not work.

     

    Shalom!,

    Michael Lee

  • October 21, 2004 at 2:32 am

    #527416

    Look at the adox help on refreshing the views collection.

    Doevents is a red herring. It does work, but not in the way you expect. The typical use of doevents is when you open a form to get parameters and then pick them up. Typical (pseudo) code is:

    open form
    form has an OK button which hides the form
    in the the calling program, while the form is visible
    do events
    (this allows the operating system to get on with things)
    process parameters from form

    Hope that helps.

     

  • October 21, 2004 at 6:49 am

    #527445

    What you are looking for is :

    Application.RefreshDatabaseWindow

  • October 21, 2004 at 9:27 am

    #527487

    We had an issue where our Access code appeared to execute faster than what our LAN could handle and we had to add code to actually have the application pause for a second before trying to access new objects.  This is the only thing that would work.  Everything would work fine if stepping through the code, but when executed normally, it would fail because when it checked for the object, it would appear to not be there.  When stepping through the code, it would always be there, so we just added a piece of code that would pause for a second and then continue with the execution of the normal code and everything was fine.  I have no explanation for this behavior, but it worked.  Seems pretty crappy to have to add code to intentionally add execution time to an app!!!

  • October 21, 2004 at 12:09 pm

    #527521

    Thanks for all the tips. I know it is not a timing issue, because I can enter debug and wait as long as i wish, it still will not find the View.

    The RefreshDatabaseWindow will not work for Views, for some reason (seems to be a lot of Googles on that subject w/o any solution)

    I don't even begin to understand ADOX, but I am looking into that solution.

     

    Y'all are great!

    Shalom!,

    Michael Lee

  • October 21, 2004 at 2:10 pm

    #527539

    Well, you should not be creating VIEWS.  You should be using a stored procedure and passing the parameters into it for execution.

    Also, either you are missing some of the SQL or it is just designed wrong in your code you gave us.

    1    Set ADOCmd = New ADODB.Command

    2    Set ADOCmd.ActiveConnection = Dbs

    3    SQL = "CREATE VIEW " & QueryName(UBound(QueryName)) & " AS " & SQL

    4    ADOCmd.CommandText = SQL

    On line 3 you have SQL being Appended to the string, but no value for it.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply