Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2004
    Posts
    16

    Question Unanswered: Recordsets in memory

    Is it possible with ms access to make "tables" in the memory?

    I do want to load two tables into different recordsets, and then make a "table" in the memory as recordsource for a report.

    So there will be fields that are not related to physical tables.

    The reason for this question is:

    I have some heavy queries that I want to perform without reading from the disk every now and then.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    From Access Help files....

    The RecordSource property setting can be a table name, a query name, or an SQL statement.
    Access doesn't allow you to use a "temp" table or recordset object.

    Is there any way for you to use a union or nested query to link your tables together? Otherwise, you may have to create a table to hold the data, and then remove it again after the report runs.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Sep 2004
    Posts
    16
    Yes all this can be done in normal queries etc but it is veeery slow. So I made some DAO recordsets wich improved speed alot, but still some recordsets will be loaded on each record (to calculate). So I was hoping to speed up by putting calculations in temporary "tables" in the memory)

  4. #4
    Join Date
    Apr 2002
    Location
    Portugal
    Posts
    146
    Hi,

    It's less heavy a query than a table, if the query is well structered, of course, if you query the all table, basicaly you are doing the same thing as open the table.

    If you make a query with criteria, you can get good results.

    I have three front-ends sharing the same back-end, there are 5 users working with those applications, which runs heavy querys all the time, with no problem in a multiuser enviroment.

    Note that you can make querys from querys if you can not handle with all the criteria in one query.

    Have you imagine if it was possible to do what you are asking, charging a table into the RAM!? your computer would imediatly freeze.

    That kind of tricks were used in applications programed in Turbo pascal or Borland C, that when you wanted a quick response from the file you were opening, you have to dimension arrays to have in memory the last results of the query to the file.

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Here's how I would do it (right or wrong)...

    All in code:

    Create a table
    Populate the table one record at a time by looping through the recordsets and performing calculation
    Open and Print the report
    Clean up the recordsets
    Delete the table
    Inspiration Through Fermentation

  6. #6
    Join Date
    Sep 2004
    Posts
    16
    Thanx for your replies. I have to try with temp. tables.

  7. #7
    Join Date
    Sep 2004
    Posts
    16
    I have now tried to make this temporary table, but this was a slower method than my previous.

    Conclusion: MS Access is slow.

  8. #8
    Join Date
    Sep 2004
    Posts
    16
    Anyway here is the code for my temporary table:

    Private Sub Report_Open(Cancel As Integer)
    Dim rstProjectHours As DAO.Recordset
    Dim rstEngineers As DAO.Recordset
    Dim varStartWeek As Integer
    Dim varEndWeek As Integer
    Dim varStartWeekDay As Integer
    Dim varEndWeekDay As Integer
    Dim varStartWeekRange As Integer
    Dim varEndWeekRange As Integer
    Dim varYear As Integer
    Dim intWeekDay As Integer
    Dim varWeekLocked As Boolean
    Dim varADD_FirstWeek As Integer
    Dim varSUB_LastWeek As Integer
    Dim varAccu As Double
    Dim strSearch As String
    Dim strSearch1 As String
    Dim strSearch2 As String
    Dim strSearch3 As String
    Dim strPH As String
    Dim i As Integer
    Dim strNot_Locked As String

    'var input til function

    varStartWeek = Week([Forms]![frmProjectHours_Report_By_Project]![txtStartDate])
    varEndWeek = Week([Forms]![frmProjectHours_Report_By_Project]![txtEndDate])
    varStartWeekDay = WeekDay([Forms]![frmProjectHours_Report_By_Project]![txtStartDate], vbMonday)
    varEndWeekDay = WeekDay([Forms]![frmProjectHours_Report_By_Project]![txtEndDate], vbMonday)
    varYear = Year([Forms]![frmProjectHours_Report_By_Project]![txtStartDate])
    varADD_FirstWeek = 0
    varSUB_LastWeek = 0

    DoCmd.RunSQL "delete * from tbl_rptProjectHours_Report_Engineer_WeekLock", -1


    Set rstProjectHours = CurrentDb.OpenRecordset("SELECT tblEventResources.Engineer_ID, tblProjectHours.ProjectHours_Monday AS PH_1, tblProjectHours.ProjectHours_Tuesday AS PH_2, " _
    & " tblProjectHours.ProjectHours_Wednesday AS PH_3, tblProjectHours.ProjectHours_Thursday AS PH_4, tblProjectHours.ProjectHours_Friday AS PH_5, " _
    & " tblProjectHours.ProjectHours_Saturday AS PH_6, tblProjectHours.ProjectHours_Sunday AS PH_7, tblProjectHours.WeekNumber, tblProjectHours.Year" _
    & " FROM tblEventResources INNER JOIN tblProjectHours ON tblEventResources.EventResourceID = tblProjectHours.EventResourceID;")


    Set rstEngineers = CurrentDb.OpenRecordset("SELECT tblEngineers.Engineer_ID, [FirstName] & ' ' & [LastName] AS Name " _
    & " FROM tblEngineers " _
    & " WHERE (((tblEngineers.InActive) = False)) " _
    & " ORDER BY tblEngineers.Engineer_ID;", dbopendynaset)


    rstEngineers.MoveFirst

    Do While Not rstEngineers.EOF
    varAccu = 0
    For intWeekDay = 1 To 7
    If (intWeekDay < varStartWeekDay) Then
    varADD_FirstWeek = 1
    Else
    varADD_FirstWeek = 0
    End If

    If (intWeekDay > varEndWeekDay) Then
    varSUB_LastWeek = 1
    Else
    varSUB_LastWeek = 0
    End If

    strSearch1 = "(Engineer_ID= '" & rstEngineers!Engineer_ID & "')"
    strSearch2 = "(WeekNumber Between " & (varStartWeek + varADD_FirstWeek) & " And " & (varEndWeek - varSUB_LastWeek) & ")"
    strSearch3 = "(Year= " & varYear & ")"
    strSearch = strSearch1 & " AND " & strSearch2 & " AND " & strSearch3
    rstProjectHours.FindFirst strSearch

    Do Until (rstProjectHours.EOF Or rstProjectHours.NoMatch)
    Select Case intWeekDay
    Case 1
    varAccu = varAccu + Nz(rstProjectHours!PH_1)
    Case 2
    varAccu = varAccu + Nz(rstProjectHours!PH_2)
    Case 3
    varAccu = varAccu + Nz(rstProjectHours!PH_3)
    Case 4
    varAccu = varAccu + Nz(rstProjectHours!PH_4)
    Case 5
    varAccu = varAccu + Nz(rstProjectHours!PH_5)
    Case 6
    varAccu = varAccu + Nz(rstProjectHours!PH_6)
    Case 7
    varAccu = varAccu + Nz(rstProjectHours!PH_7)
    End Select
    'MsgBox rstEngineers!Engineer_ID & " Timer: " & varAccu & " Startweek: " & (varStartWeek + varADD_FirstWeek) & " End Week: " & (varEndWeek - varSUB_LastWeek) & " Year: " & varYear & " Day: " & intWeekDay
    rstProjectHours.FindNext strSearch
    Loop

    Next
    varAccu = Round(varAccu, 2)
    'Insert into temporary table

    strNot_Locked = ""
    For i = varStartWeek To varEndWeek Step 1
    If Nz(DLookup("[Locked]", "tblProjectHours_WeekLock", "[Engineer_ID] = '" & rstEngineers!Engineer_ID & "' and [Week] = " & i & " and [Year] = " & varYear & "")) Then

    Else
    If strNot_Locked = "" Then
    Else
    strNot_Locked = strNot_Locked & ", "
    End If
    strNot_Locked = strNot_Locked & Str(i)
    End If
    Next

    DoCmd.RunSQL (" INSERT INTO tbl_rptProjectHours_Report_Engineer_WeekLock " _
    & "(Engineer_ID, Name, Not_Locked, Hours) VALUES " _
    & "('" & rstEngineers!Engineer_ID & "', '" & rstEngineers!Name & "', '" & strNot_Locked & "', '" & varAccu & "' );")

    rstEngineers.MoveNext
    Loop

    rstProjectHours.Close
    rstEngineers.Close

    End Sub

  9. #9
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    RedNeckGeek (interesting combo BTW) wont creating tabkles and deleting them on a regular basis bloat the MySys objects and the DB size untill a Compact/Repair is done?
    Darasen

  10. #10
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by Darasen
    RedNeckGeek (interesting combo BTW) wont creating tabkles and deleting them on a regular basis bloat the MySys objects and the DB size untill a Compact/Repair is done?
    Good Point. I always compact on close, to avoid that.
    Inspiration Through Fermentation

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i also do a lot of compact on close - one negative with this is that it decompiles your queries (which is not a problem for me since most of my queries are code-generated SQL passthrus to mySQL or SQL-Server).

    for someone using .MDB backend, compact may have a negative performance aspect.

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •