Create Queries through code - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Create Queries through code

Author

Topic

Woody
Starting Member

8 Posts

Posted - 2004-11-04 : 20:36:38

Hi,

I was wondering if it is possible to create queries using code??? I know you can create tables using "CREATE table Test;" for example, but is it possible to do the same thing with queries???

Thanks,

Alan.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-04 : 20:38:41

You mean like this:

SELECT * FROM Test

Tara

Woody
Starting Member

8 Posts

Posted - 2004-11-04 : 20:43:31

Well yes and no... I mean under access you've got Tables and Queries... I want to be able to create a query and store it in access...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-04 : 21:34:50

You mean you'd like to take a SQL query statement like "SELECT * FROM Test" and store it in an Access table? Why?

Woody
Starting Member

8 Posts

Posted - 2004-11-04 : 21:48:44

No i don't want to store it in a table... Okay you know under Access theres the "Tables" section and theres the "Queries" Section and if you click on "Create query in Design view" you can go through and create a query and save it... Well i want to do the same thing, but in code... Same thing with MS-SQL how you have Tables and Views... Can you create a View using code???

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-04 : 21:53:30

Ahhhh. Yes, you can do this in Access using Visual Basic. You want to use a QueryDef object. It has a property, SQL, that contains the SQL code you want the query to run. You can use the following:

Dim qry as new QueryDef
qry.Name = "NewQuery"
qry.SQL = "SELECT * from myTable;"
CurrentDB.QueryDefs.Add qry

This code will almost certainly need to be cleaned up...if not, you owe me a

But if you look in the MS Access help file you'll find all the documentation for Data Access Objects (DAO), the QueryDef object will be there and will have an example.

Woody
Starting Member

8 Posts

Posted - 2004-11-04 : 23:06:04

Ahhh now we are heading in the right direction, thanks Rob... Now the quesiton is, is it possible to do this in either ASP or VB.NET??? Under the VB.NET documentation the QueryDef function is only available for C++.NET... :(

I did look under Access and found the QueryDef examples... But i would like either ASP or VB.NET to create the Queries because i am writing an application and if i want to add more queries it would be much easier by code as i will not have direct access to the Access database...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-05 : 07:10:10

DAO is a COM library, so you should be able to use it in .Net just like any other COM object. I'm not familiar enough with the details, but I know it's possible. The .Net documentation should have a number of examples you can modify.

Woody
Starting Member

8 Posts

Posted - 2004-11-06 : 15:20:54

I had a look and found many examples but they didn't work properly as most examples where for VB6... BUT i did find and test this... Now this is for ASP..


Dim rsCreateQuery

strSQL = "CREATE VIEW AlanQuery AS Select Top 10 * from Events WHERE FlyDate = #10/1/04#;"
Set rsCreateQuery = Server.CreateObject("ADODB.Recordset")
rsCreateQuery.Open strSQL, objConn, adOpenStatic

rsCreateQuery.Close
Set rsCreateQuery = Nothing

It shows that there is an SQL command to create Queries/Views in either Access or MS-SQL... I believe this will also work for a few other Databases as well...

Thanks,

Alan.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources