Results 1 to 7 of 7

Thread: Memory Problem

  1. #1
    masterchef
    Guest

    Memory Problem

    Created a simple invoicing database in Access 2000 using a form/subform. With a report using the same report/subreport format. Keep getting not enough memeory to update display when printing report, This is on a win 98 box. Read the knowledge base, upgrade to office SP1a, 128 MB Ram. This all helped but when printing many or large invoices problem reoccurs. Can not duplicate problem on w2000 box with full version of office. The Invoice is just text no graphics. Any clues?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Memory Problem

    There are a couple of things you can do to remove some sludge from your database. One is to open every table in design view, open its properties dialog, and set subdatasheets to [none]. That will reduce the number of query layers you have all unawares if you created relationships in the relationships window. The next is to open the Tools-->Options dialog, select the General tab and turn off Name AutoCorrect entirely. That can create a lot of database bloat and tends to interfere with reports, among other things. Also, make sure that the queries behind your report and subreport contain only the fields you need for the report or subreport. They should NOT be the same query.

    If you do all that and nothing changes, post again.
    Charlotte

  3. #3
    Lounger
    Join Date
    Mar 2001
    Location
    LA, California, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memory Problem

    Is there a way to set the subdatasheet property to [none] for all tables using VBA?
    Thanks,
    Sean

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Memory Problem

    Here's some DAO code to do it. There is also code in the Code Librarian that comes with Office 2000 Developer. This version allows you to specify a particular table if you wish. If I remember correctly, I rewrote this in ADO as well, so if you need that, yell.

    <pre>Public Function TurnOffSubdatasheets(ParamArray TblName() As Variant)
    'Created by Charlotte Foust 9/26/2000
    'Calls: SetAccessProperty function
    On Error Resume Next

    Dim blnOK As Boolean
    Dim intLoop As Integer
    Dim intChanged As Integer
    Dim strTblName As String
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef

    TurnOffSubdatasheets = True

    'If table name or names passed,
    'initialize the strTblName variable
    If Not IsMissing(TblName()) Then
    strTblName = TblName(0)
    End If

    Set dbs = CurrentDb()
    If strTblName = "" Then
    'If not table names passed then
    'loop through the tabledefs collection
    For Each tdf In dbs.TableDefs
    'skip the system tables
    If Not (tdf.Attributes And dbSystemObject) Then
    If tdf.Properties("SubdatasheetName") <> "[None]" Or _
    Err = 3270 Then
    Err.Clear
    blnOK = SetAccessProperty(obj:=tdf, _
    strName:="SubdatasheetName", _
    intType:=dbText, _
    varSetting:="[None]")
    If blnOK Then
    intChanged = intChanged + 1
    Else
    TurnOffSubdatasheets = False
    End If
    Else
    blnOK = True
    End If
    End If
    Next
    Else
    For intLoop = 0 To UBound(TblName)
    'loop through the passed table names
    'and set the subdatasheet name property
    strTblName = TblName(intLoop)
    Set tdf = dbs.TableDefs(strTblName)
    If tdf.Properties("SubdatasheetName") <> "[None]" Or _
    Err = 3270 Then
    Err.Clear
    blnOK = SetAccessProperty(obj:=tdf, _
    strName:="SubdatasheetName", _
    intType:=dbText, _
    varSetting:="[None]")
    If blnOK Then
    intChanged = intChanged + 1
    Else
    TurnOffSubdatasheets = False
    End If
    Else
    blnOK = True
    End If
    Next intLoop
    End If
    Proc_exit:
    On Error Resume Next
    TurnOffSubdatasheets = intChanged
    Set tdf = Nothing
    Set dbs = Nothing
    End Function

    '*******************************
    Function SetAccessProperty(obj As Object, strName As String, _
    intType As Integer, varSetting As Variant) As Boolean
    'From on-line help
    Dim prp As DAO.Property
    Const conPropNotFound As Integer = 3270

    On Error GoTo ErrorSetAccessProperty
    ' Explicitly refer to Properties collection.
    obj.Properties(strName) = varSetting
    obj.Properties.Refresh
    SetAccessProperty = True

    ExitSetAccessProperty:
    Set prp = Nothing
    Exit Function

    ErrorSetAccessProperty:
    If Err = conPropNotFound Then

    ' Create property, denote type, and set initial value.
    Set prp = obj.CreateProperty(strName, intType, varSetting)
    ' Append Property object to Properties collection.
    obj.Properties.Append prp
    obj.Properties.Refresh
    SetAccessProperty = True
    Resume ExitSetAccessProperty
    Else
    MsgBox Err & ": " & vbCrLf & Err.Description
    SetAccessProperty = False
    Resume ExitSetAccessProperty
    End If
    End Function
    '**********************************</pre>

    Charlotte

  5. #5
    Lounger
    Join Date
    Mar 2001
    Location
    LA, California, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memory Problem

    Thanks. I have been working on a number of projects, so I haven't been able to try this yet, but I looked through it and it looks like it's just what I need. I already got rid of autocorrect from reading about it in your post and in previous WAW articles, so hopefully I'll squeeze a little more speed and size out of my database.

  6. #6
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memory Problem

    I read once in Cary N Prague's Access 97 Secrets (ISBN 0-7645-3043-7) that using lines on Reports will use up system resources which are not recovered when you close the Print Preview window. Use rectangles with a height of 0 to work around the problem. Just a thought.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Memory Problem

    I believe that was an Access 2.0 problem rather than Access 97, but I don't recall clearly. In any case, it no longer applies in Access 2000 as far as I can tell.
    Charlotte

Posting Permissions

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