Create a Query from VB6 and store it in DB (Acs 2003) - Runtime

[VBFnewcomer]

Hello everybody,
How do we create a query in runtime from VB6 and save it to an existing database (Access 2003)

Thankyou

[Chathura]

Before trying to create a query you need to specify what type of queries you are going to allow. And you must describe the scenario. If you are trying to just write a query in a text box, then you only have to save it to the Database. But if you are going to build it with given keywords and/or if you want to specify what is the database and what are the tables/columns, then you'll have to design an interface and do the codings.

[Hack]

You would need to save the query to a string and save the string to your database as text.

I have done similiar things, but save them to an external text file. The one big problem with this is that you have no variables in them. The query will be saved as literal values so if it contains dates, the date won't change.

[BruceG]

I don't have the exact syntax at hand, but back in the day, I used DAO within the VB app to create a QueryDef object in the Access database, for subsequent use in a Crystal Report. You could still use DAO objects, or the ADO equivalent, to create or modify a query in an Access database. The main point is, you would typically do so for the purpose of immediately subsequently using the query as the basis for a report. (I mentioned Crystal Reports, but you could also apply the same idea if you wanted to run an Access report from your VB app, and the standard way of doing so, i.e. the "DoCmd" to run the report with a "where" clause created dynamically was not quite adequate for your needs.)

[RobDog888]

What I do is to execute a sql statement that will create the query in the acccess db. Then call the query passing any parameters in a command object.

Use the CREATE VIEW syntax for your sql statement and execute off of your ADO connection object.

[VBFnewcomer]

Use the CREATE VIEW syntax for your sql statement and execute off of your ADO connection object.

Yes I have something like that in mind. But the Problem is I neither know the syntax/procedure to accomplish the feat nor aware of the references that I may have to add to the proj to accomplish this. I would be happy if you could give me a small example.

[RobDog888]

Sure, use ADO (tutorial in the DB forum FAQ, and from your connection object...

Something like so:
oConn.Execute "CREATE VIEW qryVBF AS SELECT Field1, Field2, Field3 FROM Table1;"

[VBFnewcomer]

I tried to run the below code

conn.Execute "CREATE VIEW QryFromVB AS SELECT fName, mName, lName, DOB From CounselMaster ORDER BY fName;"

I got error Only simple SELECT queries are allowed in VIEWS what does this mean how do I make a query which has sorted data.