Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    40

    Unanswered: Create an SQL View from VBA

    Hello All;

    I'm new to SQL, and developing my first application using SQL 2000 as the back end for my data.

    Can anyone tell me if I can create an SQL View from VBA?

    I'm trying to create a data view that access various records from a single table, and it has to distribute that data 2 14 different subforms, representing different days in a 2 week period, which is distingiushed by a field called "Day", that is numbered from 1 to 14.

    I also have a summary subform that has the weekly summary (days 1 to 7 and 8 to 14) on seperate subforms. In total I have 16 subforms, which actually source from a single table, just split depending on the day in the period.

    As I see it now, creating views iis the best way for me to go, but I also have to be able to change the period id on the fly, so I'm think I have to use VBA to generate these views.

    Does any of this make sense, and am I on the right track??

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    You might want to consider a more dynamic solution. Do the 14 forms all hold the same data fields? If so, why not use one form and base the contents on the day of a week. Make the day of the week a field in your table and populate your form based on a stored procedure that uses the day of the week as a parameter in the query.

    Fixing your schema now will pay you back many fold in the future.

    Avoid creating database objects on the fly in end user applications.

    To answer your question, yes this is possible. Is it a good idea? No.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Thrasymachus
    ... populate your form based on a stored procedure that uses the day of the week as a parameter in the query.

    Fixing your schema now will pay you back many fold in the future.

    Avoid creating database objects on the fly in end user applications.

    To answer your question, yes this is possible. Is it a good idea? No.
    Agreed with all the points here. You might also consider a user defined function that returns a table. I don't generally use these for multi-column result sets, but it is permissible to do so.

    Perhaps you could post some ddl and sample data and improve your chance for getting a useable answer...

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •