Out of Diskspace or Memory Error

Hello,

I'm working with an Access 2000 database which is 538MB in size.

When I try to change the field size of a text field from the default size of 255 bytes, I'm getting the following error:-

   Microsoft Access can't change the  
   data type

   There isn't enough disk space or    
   memory.

I'm running the database on an IBM Pent. III 450Mhz PC with 160MB RAM, I've got a 8GB Hard Drive with 4.6GB free.

The database (538MB) contains 11 tables.

How can I resolve this problem???

Any suggestions??


Thanks..


msawill
msawillAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lmerrellCommented:
msawill,

Sorry it took so long to respond.  I have encountered this problem many times in the past.  The easiest solution is to highlight and copy the table from the database window.  Then "paste" the copy in the database window.  You will be prompted for the Table Name and Paste Options.  Put something like MyTempTable for name and Structure Only for Paste Options.  Then change the fields size in the "empty" table.  Next append all records from the original table.  Rename the original to something like MyOldTable.  Rename the new table to the original name.  When you are satified with the new table you can delete MyOldTable.  It works like a dream.

lmerrell
0
lmerrellCommented:
If the table is too large to "copy" another way to create the Temp Table is to create a make table query with the Top Value property set to 1.  Run the query then manually delete the lone record.
0
CareyMBilyeuCommented:
The problem you have here is a simple regedit fix away. Follow the instructions below:

ACC2000: Error: "There Isn't Enough Disk Space or Memory"
ID: Q209940

 

--------------------------------------------------------------------------------
The information in this article applies to:

Microsoft Access 2000

--------------------------------------------------------------------------------
IMPORTANT: This article contains information about editing the registry. Before you edit the registry, make sure you understand how to restore it if a problem occurs. For information about how to do this, view the "Restoring the Registry" Help topic in Regedit.exe or the "Restoring a Registry Key" Help topic in Regedt32.exe.

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).



SYMPTOMS
When you perform an operation on a table, you may receive the following error message if the operation creates a large number of page locks:

There isn't enough disk space or memory.
If you run an action query on a large table, you may receive the following error message:
There isn't enough disk space or memory to undo the data changes this action query is about to make.



CAUSE
The page locks required for the transaction exceed the MaxLocksPerFile value, which defaults to 9500 locks. The MaxLocksPerFile setting is stored in the Windows Registry.



RESOLUTION
There are several ways to work around this behavior:

You can use Regedit.exe to edit the Windows Registry and change the MaxLocksPerFile value permanently.


You can use the SetOption method of the DBEngine object to change the MaxLocksPerFile value temporarily in code.


If the error occurs when you run an action query, you can modify the query and set its UseTransaction property to No.


Method 1: Changing MaxLocksPerFile in the Windows Registry
WARNING: Using Registry Editor incorrectly can cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.

For information about how to edit the registry, view the "Changing Keys and Values" Help topic in Registry Editor (Regedit.exe) or the "Add and Delete Information in the Registry" and "Edit Registry Data" Help topics in Regedt32.exe. Note that you should back up the registry before you edit it. If you are running Windows NT, you should also update your Emergency Repair Disk (ERD).

Use Registry Editor to increase the MaxLocksPerFile value under the following key:

   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0
Note that this method changes the Windows Registry setting for all applications that use the Microsoft Jet database engine version 4.0.
Method 2: Using SetOption to Change MaxLocksPerFile Temporarily
NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you need to reference the Microsoft DAO 3.6 Object Library.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:


http://www.microsoft.com/mcsp/
For more information about the support options available from Microsoft, please see the following page on the World Wide Web:


http://www.microsoft.com/support/supportnet/overview/overview.asp
The SetOption method temporarily overrides values for the Microsoft Jet database engine keys in the Windows Registry. The new value remains in effect until you change it again, or until the DBEngine object is closed.

NOTE: Changes made to the MaxLocksPerFile setting using the SetOption method will only be available through the current session of Data Access Objects (DAO). Queries run through the Microsoft Access user interface, will still use the settings within the registry.

The following code sample sets MaxLocksPerFile to 200,000 before executing an update operation inside a transaction:

Sub LargeUpdate()
   On Error GoTo LargeUpdate_Error
   Dim db As DAO.Database, ws As DAO.Workspace

   ' Set MaxLocksPerFile.
   DBEngine.SetOption dbMaxLocksPerFile, 200000

   Set db = CurrentDb
   Set ws = Workspaces(0)

   ' Perform the update.
   ws.BeginTrans
   db.Execute "UPDATE LargeTable SET Field1 = 'Updated Field'", _
      dbFailOnError
   ws.CommitTrans

   db.Close
   MsgBox "Done!"
   Exit Sub

LargeUpdate_Error:
   MsgBox Err & " " & Error
   ws.Rollback
   MsgBox "Operation Failed - Update Canceled"
   Exit Sub
End Sub
Method 3: Setting the UseTransaction Property in an Action Query
If a stored action query causes the error, you can set its UseTransaction property to No. Note that if you do this, you will not able to roll back your changes if there is a problem or an error while the query is executing:
Open the query in Design view.


On the View menu, click Properties.


Click an empty space in the upper half of the query window to display the Query Properties dialog box.


Set the UseTransaction property to No.


Save the query and close it.





MORE INFORMATION
The MaxLocksPerFile setting in the Windows Registry prevents transactions in the Microsoft Jet database engine from exceeding a specified value. If a transaction tries to create locks in excess of the MaxLocksPerFile value, the transaction is split into two or more parts and partially committed. This feature was added to Microsoft Access 97 to prevent Netware 3.1 server crashes when the specified Netware lock limit was exceeded, and to improve performance with both Netware and Microsoft Windows NT.

Steps to Reproduce Behavior
The following example uses a Visual Basic procedure to create a table with 10,000 records in it, and then modifies the table in order to cause the error message:
Open the sample database Northwind.mdb.


Create a module and type the following procedure:



Sub CreateBigTable()
   Dim db As Database, rs As Recordset
   Dim iCounter As Integer, strChar As String
   Set db = CurrentDb
   db.Execute "CREATE TABLE BigTable (ID LONG, Field1 TEXT(255), " & _
     "Field2 TEXT(255), Field3 TEXT(255), Field4 TEXT(255))", _
     dbFailOnError
   Set rs = db.OpenRecordset("BigTable", dbOpenDynaset)
   iCounter = 0
   strChar = String(255, " ")
   While iCounter <= 10000
      rs.AddNew
      rs!ID = iCounter
      rs!Field1 = strChar
      rs!Field2 = strChar
      rs!Field3 = strChar
      rs!Field4 = strChar
      rs.Update
      iCounter = iCounter + 1
   Wend
   MsgBox "Done!"
End Sub
To run the procedure, type the following line in the Immediate window, and then press ENTER:

CreateBigTable

The procedure creates a table called BigTable with 10,000 records in it.


Save the module as Module1 and close it.


Open the BigTable table in Design view.


Change the FieldSize property of Field4 to 253.


Save the table. Click Yes when you are prompted that some data may be lost. Note that, after a while, you receive the following error messages:


Microsoft Access can't change the data type.
There isn't enough disk space or memory.
Errors were encountered during the save operation. Data types were not changed. Properties were not updated.



REFERENCES
For more information about the SetOption method, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type "SetOption method" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about customizing registry settings for the Jet database engine, click Microsoft Access Help on the Help menu, type "Customizing Windows Registry settings for Microsoft Jet" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about the UseTransaction property, click Microsoft Access Help on the Help menu, type "UseTransaction property" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.


Additional query words: prb MaxLocksPerFile Netware


Keywords          : kberrmsg kbdta
Version           : WINDOWS:2000
Platform          : WINDOWS
Issue type        : kbprb


0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

CareyMBilyeuCommented:
By the way: The default value 9500 is the same for Access 957, 97 and 2000. You might run into this problem alot when you are trying to change a text field to numeric, modify a date field, etc. In developing the database for distribution, I usually modify my settings throught the registry and then once I am done with the table portion of development/modification, I then go back to the registry and return the value to 9500 (default).

Carey
0
CareyMBilyeuCommented:
Please read the comments provided above. If this does not help you, please reject the answer and resubmit your question.

Carey
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShaneTampaCommented:
Or, in the table you wish to modify, you could create a new field of the type and size you want, run an update query to populate the newly created field, then go back into the table's design view, delete the old field and rename the new field to the old field's name.
0
CareyMBilyeuCommented:
I can't believe that you graded the answers provided as "C".
0
lmerrellCommented:
Carey,

You've been autograded by EE.  It always grades as a C.  Not quite fair to the experts.

lmerrell
0
msawillAuthor Commented:
Attn: CareyMBilyeu

I'm sorry if you were given a grade "C" in response to your answer, I do believe that your answer was worth an "A+". It certainly solved the problem I was experiencing. I'm also very grateful for the detailed information and explanation supplied.

Once again, my apologies and gratitude to you.

Msawill
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.