I'm connecting to an existing access database via VB6. There are two tables in the database, both with a primary key of VehicleID. Table 1 holds model information for the vehicle like the plate, the RPM of the motor, etc. Table 2 has the test results.
If I want to see how a vehicle tested in the shop, I have to open up the DB, take a look at Table1, find the vehicle I want, look at the VehicleID number, and open up table 2. I then have to find the same VehicleID number in table 2 to get the results. There could be multiple records in table 2 for the different test points as well.
Notes: I can't change the database, some hardware relies on it being the same, so I'm trying to make a workaround and pull a few key points of data out of the DB.
Based on my tables, I've made the following query:
Table 1: PK = VehicleID
Table 2: PK = VehicleID
SELECT TOP 10 * FROM Vehicle INNER JOIN VehicleResults on Vehicle.VehicleID = VehicleResults.VehicleID ORDER BY TestDate DESC
The system always updates table 2 with the most upto date results by overwriting the existing records. I'm just going to pull the top 10 results from the DB, purge the ones I don't want in VB, and output what I want to an excel file.
The query does exactly that, but its slow when searching through all of my records, and I'm only looking for a few fields, so the size of the recordset is kind of excessive(40 fields).
I was thinking about trying to limit the results by using a WHERE, based on the date to pull the top ten for todays date, but the field in the database is formatted with an exact time stamp so I'm not sure how to do a where based on that.
Are there any better approaches to what I'm trying to do?