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

Create a view in VBA

Hi,

Is it possible to create some SQL in VBA, 'run it' then view as a datasheet
the results?

I'm trying to throw together a fairly large search form, and VBA seems the
best way to parse the parameters.

Cheers,
Chris"Not Me" <Not.Me@.faker.fake.fa.kee> wrote in message
news:c6rag7$kt$1@.ucsnew1.ncl.ac.uk...
> Hi,
> Is it possible to create some SQL in VBA, 'run it' then view as a
datasheet
> the results?
> I'm trying to throw together a fairly large search form, and VBA seems the
> best way to parse the parameters.
> Cheers,
> Chris

Yes - you could use the ADO COM objects, then return results to your client
and format/present them there. If you need a more precise answer, then it
would help to have some more detail about what you're trying to do and what
tools you're using (eg. Access, Excel etc.).

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:40913ac0$1_1@.news.bluewin.ch...
> "Not Me" <Not.Me@.faker.fake.fa.kee> wrote in message
> news:c6rag7$kt$1@.ucsnew1.ncl.ac.uk...
> > Hi,
> > Is it possible to create some SQL in VBA, 'run it' then view as a
> datasheet
> > the results?
> > I'm trying to throw together a fairly large search form, and VBA seems
the
> > best way to parse the parameters.
> Yes - you could use the ADO COM objects, then return results to your
client
> and format/present them there. If you need a more precise answer, then it
> would help to have some more detail about what you're trying to do and
what
> tools you're using (eg. Access, Excel etc.).

Thanks for the reply.
In the past, I have been able to pre-write a query in Access, (i.e. have it
saved in the queries list) and use docmd.openquery in VBA to have it
displayed on screen.
I have also been able to create queries 'on the fly' in VBA, and be able to
iterate through them/work on them in the same program.
What I'd like to do is sort of combine the above, so create a query in VBA
and have it displayed in a datasheet view. So the user in an ADE would
select from a few comboboxes, press go and up would pop the resulting query.

Cheers,
Chrissql