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??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.|||... 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