[RESOLVED] Querys

[Dbee]

I was looking at a database in access and it had all kinds of querys listed in it.

Was wondering how you would create a query like that using Vb. Without using the wizard in Access to do so.

[si_the_geek]

I'm not entirely sure what you mean... if you mean you want to have a query inside your program that runs against the database, you can simply specify it when opening a recordset, eg:

Dim strSQL as String
Dim objRS as ADODB.Recordset
  strSQL = "SELECT Field1 " _
         & "FROM Table1 " _
         & "WHERE Field2 = value"
  objRS.Open strSQL, objCN, adOpenForwardOnly, adLockReadOnly, adCmdText
...

(note that doing this is almost always better than opening an entire table)

If you mean that you want to add a query to the Access database (so that you can run it in Access), there are ways to do that.. but it seems that the method I expected (running a Create View statement) is not apt.

[brucevde]

If you are using DAO, create a QueryDef object and add it to the database.

Dim db As DAO.Database
Dim qry As New DAO.QueryDef

Set db = DBEngine.OpenDatabase("C:\Projects\northwind2002.mdb")
    
qry.SQL = "Select * From Customers"
qry.Name = "qryTest"
    
db.QueryDefs.Append qry
db.Close

Create View works for me using ADO (2.8), the Jet 4.0 provider and Access 2002.

Dim db As ADODB.Connection
Dim strSQL As String

Set db = New ADODB.Connection
db.Open "provider=microsoft.jet.oledb.4.0;data source=c:\projects\northwind2002.mdb"
    
strSQL = "Create View qryTest2 As Select * From Customers"
    
db.Execute strSQL, , adCmdText Or adExecuteNoRecords
    
db.Close

Another option might be ADOX.

[Dbee]

Thanks brucevde

That what I was looking for. A method of creating a querys out side of access

Once you create a query what would be the link call for say 3 querys together.

The reason I am asking all these dumb questions is I created a program for a company to access different information in there database to created a visual display of there storage site. Thats when I found the querys and the links.

[si_the_geek]

What did you mean by this:

Once you create a query what would be the link call for say 3 querys together.

Do you mean that you want to create a query which returns data from 3 tables, with the values from the tables linked as apt?

[Dbee]

Refering to link in the query to the table you are using for this query.

If that makes sense.

At present I open 3 databases for the program I am writhing with one new one created at the begging of each new year .

Thought that I might do the same thing by using querys.

[Dbee]

Tryed the Create View it loads the entire table instead of only one or two fields

[si_the_geek]

Refering to link in the query to the table you are using for this query. ...

I'm afraid it's still not clear.

It sounds like maybe you want to use the same query multiple times, each time using a different table. If so, that is not possible - you need separate queries for each.

At present I open 3 databases for the program I am writhing with one new one created at the begging of each new year .

Creating a new database each year is rarely a good idea - it is usually better to use just one one which uses a Where clause to get the apt data (assuming you have a field which contains relevant info to do that).

It is also rarely a good idea to have multiple databases, but sometimes it can be apt.

Tryed the Create View it loads the entire table instead of only one or two fields

That would be because the Select statement explicitly asks for all fields (Select *) and all rows (no Where clause).

[Dbee]

What you are saying is that if I want query with only certain fields from the table then I would have to create them in Access.

Then the relationships could be selected.

Instead of using views total limit selection to the whole table, I tryed to do a selection of certain fields in view
received error.

The reason for the creation of the yearly database is its the months of the year and it pertains to what days the employee is off or pay period of the month. I thought that if I had a crash in that database it would be much easer to recover one year instead of multi years.

[si_the_geek]

You don't need to create a query in Access, you can do the same thing directly in VB - the only difference is where the Select statement is stored (note that while Access defaults to a different interface, it just builds an SQL Select statement).

To limit the rows and/or columns that are returned, you simply alter the Select statement as apt. You can also get data from multiple tables (using the relationships) by adding Joins to the Select statement.

I would recommend reading the first tutorial in the "SQL" section of our Database Development FAQs/Tutorials (at the top of this forum), as it explains how to write Select statements, and lets you try it out too.

The reason for the creation of the yearly database is its the months of the year and it pertains to what days the employee is off or pay period of the month. I thought that if I had a crash in that database it would be much easer to recover one year instead of multi years.

Unless you have huge amounts of data (eg: over 1GB), data recovery isn't going to be much different - it is still just a matter of using the tools to repair the file.

[Dbee]

Well got another question why would they create all these querys in that database I do mean about 100 or so.

In some of them are simple references other apply to accounting.

Just asking when the same could be done by using the select

Now this database I am talking about was created with access 97.

[si_the_geek]

They are using Select statements, as that is all that Access queries are.

The big difference is where those statements are stored, in this case in a place that is easy to re-use (so for example they could use exactly the same query from 5 different forms in Access), and easy to run manually when you want to check something.

[Dbee]

Is there a way to do the same in Vb or do you have to do so in Access to create the querys.

It seems to me it was a fast way to extract data. For those querys have data in them.

Just like fields in a table.

[si_the_geek]

You can create (and run) queries in your VB code, as I showed in post #2.

Using queries is a fast way to extract data, as you are limiting the amount of data that needs to be sent to your program (and picking the rows/columns is faster, as the database system is much more efficient at it than VB code can be).

Queries do not contain data, they just contain instructions on what data to get. Within Access you can execute a query, and the results are displayed on screen in a grid interface (similar to how tables are displayed), whereas when you execute a query from code the results are stored in a recordset.

[Dbee]

When I went to design view for the query I found relationships to the tables
That I assume is where the data for that query come from.

Ok Thats All you answered my questions

Thanks alot.

[si_the_geek]

The design view is just a graphical representation - if you go to the SQL view you will see the actual Select statement, which can be used in your VB code (but may need minor modifications).

As it involves relationships it will be a little more complex than what I posted, as the FROM clause will contain the information about the relationships.