Solved

Understanding VB6, Access 2000, DAO, SQL Queries

Posted on 2006-07-12
6
373 Views
Last Modified: 2013-12-25
Hello Experts;

I'm a new VB programmer and I'm writing an application to manipulate stock market data. I have an Access 2000 database that I process using VB6, referencing DAO. I want to declare two record sets and using one recordset as an input stream, cycle through the second recordset and process the data.

For the first recordset, I need to retreicve all columns of the first 25  rows sorted in decending sequence by NumFieldA.

The second recordset is the entire database.

Now, I need to find the symbol for each row in recordset 1 in recordset 2 and perform certain processing.

My question: Can anyone help me code the SQL SELECT statements to do the above? I can provide additional information if needed. Thanks for your help!

Joe
0
Comment
Question by:JoeWaynick
  • 3
  • 3
6 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 17098589
"My question: Can anyone help me code the SQL SELECT statements "
"For the first recordset, I need to retreicve all columns of the first 25  rows sorted in decending sequence by NumFieldA."

Select top 25 mytable.* from mytable order by NumfieldA desc

"The second recordset is the entire database."

I assume you mean the entire table.

Select * from mytable

Pete
0
 

Author Comment

by:JoeWaynick
ID: 17099144
Hi Pete;

I added the following code and ran into a syntax error on the first select statement:

    Set dbHist = OpenDatabase("c:\Documents and Settings\Joe\Data Files\MS-Visual Basic\SMA\HistoricalData.mdb")
    Set rs = dbHist.OpenRecordset("tblHistoricalData ", 2)
    Set rsPctChg = dbHist.OpenRecordset("tblHistoricalData ", 2)
   
    If rs.RecordCount > 0 Then
        SELECT TOP 25 rsPctChg.* FROM rs ORDER BY PercentChange desc
        SELECT * FROM rs ORDER BY DateFld, Symbol
    End If

I'm sorry to ask such basic questions, but I'm really new. Any assistance you and others can give is appreciated.

Joe
0
 
LVL 77

Expert Comment

by:peter57r
ID: 17102941
I don't really follow what you are doing in your SQL statements, but things don't work like that.
For the top 25 records you probably want:
 Set rs = dbHist.OpenRecordset("SELECT TOP 25 * FROM tblHistoricalData ORDER BY PercentChange desc", 2)

This is what you need for the 'whole table'
Set rsPctChg = dbHist.OpenRecordset(" SELECT * FROM tblHistoricalData ORDER BY DateFld, Symbol ", 2)

It looks like you need to do some reading and look at examples of recordset processing before getting much further.

Pete
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:JoeWaynick
ID: 17103632
Hi Pete;

Thanks for your response. I'll be the first to admit my weakness in understanding vb. And rest assured I'm reading daily to improve my understanding.

Yet, in spite of my unsophisticated explaination of the problem, you still seemed to have captured precisely what I'm trying to do. However, I do need to make one little tweek. I need the top 25 for each date (identified by DateFld), and I'll be cycling through the table building groups of 25 symbols day. I tried modifying your code as follows but it didn't work. Can you give me a hint as to where I'm wrong?

Your original code:    
                           Set rsPctChg = dbHist.OpenRecordset("SELECT TOP 5 * FROM tblHistoricalData
                                ORDER BY PercentChange desc", 2)

My change:
                          Set rsPctChg = dbHist.OpenRecordset("SELECT TOP 5 * FROM tblHistoricalData
                               WHERE DateFld = dteDateVar ORDER BY PercentChange desc", 2)

dteDateVar is a variable I decleared with a starting date and I will increment the date after each group of top 25 symbols. When I run my change I get:

                         "Runtime 3061. Too few parameters. Expected 1."

Thanks again for any assistance you and others can provide.

Joe

0
 
LVL 77

Accepted Solution

by:
peter57r earned 200 total points
ID: 17106722
Set rsPctChg = dbHist.OpenRecordset("SELECT TOP 5 * FROM tblHistoricalData
                               WHERE DateFld = #" & dteDateVar  & "# ORDER BY PercentChange desc", 2)
Dates must be enclosed in #..#  and you need the variable to be evaluated so it has to be 'outside' the sql string.

Pete
0
 

Author Comment

by:JoeWaynick
ID: 17107825
Pete;

Thank you for the modified code. It works like a champ. More importantly, thanks for the explaination as to "why" it works. I'll tuck that away in my toolkit. Your points are well deserved.

Joe
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now