Solved

Getting a subset of records from a DAO Recordset in Access 2007

Posted on 2014-01-29
7
1,272 Views
Last Modified: 2014-01-30
I have a recordset set to an SQL Statement that pulls from a very large Oracle table via OBDC connection.  It pulls all the data for one year (has wage and hours detail data for employees)

I need to loop through a local table of employeess and have it calculate total wages per employee, based upon a date range, department, and their employee id.  The date range
is needed when we employees who have transferred from one department to another where we use a Term Date to identify when they left a department and a Hire Date which is when they started in that department.  

I know that I could query the Oracle table every time I need to calculate the totals but it takes a very long time to pull from it (about 10 seconds per pull) -- which could result in some very long processing times when processing 7000+ employees.

Is it possible to bascially query a recordset query?  Here's a pseudo code example:

Main recordset's SQL (set to rsE_PRTIME):
Select * from LAWSON_PRTIME where Pay_Year = 2014

If there is a TERMDATE then

  Select ENumber, Sum(Wages) as TotalWages
  FROM the above referrenced recordset [rsE_PRTIME]
  WHERE  Date_Stamp > 1/1/2014 and <= TermDate (a variable)
  GROUP BY ENumber

 Post TotalWages to local table

End if
Is this even possible?  If so, I think it would speed up processing time significantly over
0
Comment
Question by:jrogersok
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 500 total points
ID: 39818483
If you are looking for a performance increase ... My experience working with various backend servers is to  ...

1) Create a Pass-Thru query to first select the records from LAWSON_PRTIME.
2) Then use that query as the basis to append the results into a Temp Table (tblTemp1) on your server.
3) In your recordset processing loop, use the DSum() function against the Temp Table to update each record with a termdate.

rst.Edit
rst!TotalWages = DSum("Wages", "tblTemp1", "Date_Stamp >#1/1/2014# And Date_Stamp <=#" &  rst!TermDate & "#")
rst.Update


ET
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39818511
Also, for step 1, you can create a view (WagesView) on SQL Server to pull the specific records from LAWSON_PRTIME.  As long as the view is linked in your Access Front-End then in your recordset processing loop you would ...

rst.Edit
rst!TotalWages = DSum("Wages", "WagesView", "Date_Stamp >#1/1/2014# And Date_Stamp <=#" &  rst!TermDate & "#")
rst.Update


ET
0
 
LVL 2

Author Comment

by:jrogersok
ID: 39818560
Thanks for the quick response.

I do know how to do this via the making a table route, but was hoping to not have to create a local table out of the data as it can take litterally hours to make the table from this data (I've tried that route).   I have tested passthrough queries in the past and didn't seem to gain much in the speed department on this database

We're not using SQL Server, but Oracle and I don't have the rights to create any views from that side, only the ODBC connection so I cannot create a View.

I'm guessing that this is not possible "in memory", only via local tables or views?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 2

Author Comment

by:jrogersok
ID: 39818675
Well,  I tried making a table from a passthrough and it wasn't as bad as I thought this time around -- about 4 minutes -- I think I can live with that.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39819283
<<<<<We're not using SQL Server, but Oracle and I don't have the rights to create any views from that side, only the ODBC connection so I cannot create a View.
I'm guessing that this is not possible "in memory", only via local tables or views?>>>

You can Filter a Recordset but I don't think you can create a SQL Select statement using a Recordset as the Table or Query.  Plus your Filter on the Recordset would not create the Sum for you.

Since you only have access to the Oracle server via ODBC then a local table would be the next alternative.  

Again, I've had much success with various databases as the backend by dumping the results of the Select query to a temp table then manipulate from there.

ET
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39820632
<<<<<Well,  I tried making a table from a passthrough and it wasn't as bad as I thought this time around -- about 4 minutes -- I think I can live with that.>>>

Also, you should be able to create your original Recordset based on the passthrough query instead of creating the temp table.

Test each method and see which performs the best.

ET
0
 
LVL 2

Author Closing Comment

by:jrogersok
ID: 39822287
Works perfectly!  Thanks - the passthrough rocks! (and so do you)
0

Featured Post

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Introduction to Processes

695 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