Unable to change data type in MS Access 2007

Question

I have a huge database (800 MB), which consists of a field called "Last Modified Date" at the moment when this field is entered as a text data type, but you need to change it to the Date / Time field to fulfill some queries.

I have another exactly the same database, but with only 35 MB of data inside it, and when I change the data type, it works fine, but when I try to change the data type in a large database, it gives me an error:

Micorosoft Office Access can't change the data type. There isn't enough disk space or memory 

After some research, some sites mentioned about modifying the registry file (MaxLocksPerFile) also tried to do this, but with no luck: - (

Can anyone help?

Answer 1

One relatively tedious (but simple) solution would be to split the large database into smaller databases, convert to smaller databases, and then recombine them.

This gives an additional advantage: if for some reason the text is an invalid date in one chunk, it will be easier to find (due to the smaller size of the chunk).

Assuming you have some kind of integer key in a table that ranges from 1 to (say) 10,000,000, you can just make queries like

 SELECT * INTO newTable1 FROM yourtable WHERE yourkey >= 0 AND yourkey < 1000000 SELECT * INTO newTable2 FROM yourtable WHERE yourkey >= 1000000 AND yourkey < 2000000 

etc.

Be sure to enter and execute these queries separately, as Access seems to give you a syntax error if you try to run more than one at a time.

If your keys are something else, you can do the same, but you will have to be a little more complicated with WHERE clauses.

Of course, the last thing to consider if you can use it is to switch to another database, which has a bit more features. I assume that you have reasons why this is not easy, but with the amount of data that you are talking about, you are likely to run into other problems, and continue to use Access.

EDIT

Since you still have some problems, here are some details in the hope that you will see what I have not described well enough before:

Here you can see that I created the "OutputIDrive" table, similar to the one you are describing. I have an identification tag, although I only have three entries.

Here I created a query, went into SQL mode and entered the appropriate SQL statement. In my case, because my request only captures the values> = 0 and <2, we just get one row ... one that has ID = 1.

When I press the start button, I get a popup that tells / warns me what is about to happen ... this puts the row in a new table. This is good ... this is what we are looking for. I click OK.

Now our new table is created, and when I click on it, we see that our one row of data with ID = 1 has been copied to this new table.

Now you can change the table name and numeric values ​​in the SQL query and run it again.

Hope this helps you with what's confusing you.

EDIT 2:

Yeah! This is a trick. You must enter and run SQL statements one at a time in Access. If you try to insert several statements and run them, you will get this error. So, start the first one, then erase it, start the second one, etc., and everything will be all right. I think it will be done! I edited the above to make this clearer.

Answer 2

As John W. Vinson says here , the problem you are facing is that Access wants to keep a copy of the table while it is making changes, and this causes it to exceed the maximum permissible size of the access file. Compaction and recovery may help to get the file under the size limit, but this did not work for me.

If, like me, you have many complex relationships and reports on the old table that you don’t want to repeat, try this option instead of @ user292452 solution:

• Copy the table (i.e. 'YourTable' ), then paste only the structure. to your database with a different name (i.e. 'YourTable_new' ).
• Copy YourTable again and paste the data into YourTable_new . (To insert-add, first insert and select Add Data to Existing Table.)
• You can make a copy of your Access database at this point, just in case something goes wrong with the next part.
• Delete all data in YourTable using a delete request --- select all fields using an asterisk, and then start with the default settings.
• Now you can change the fields in YourTable as needed and save again.
• Paste-add data from YourTable_new to YourTable and check for type conversion errors, lengths, etc. did not have.
• Delete YourTable_new .

Answer 3

I had this problem.

disk space and available memory were ok.

the problem disappeared after being "compact and fixed."

coincidence?

e.

Answer 4

Adapted from Karl Donaubauer's answer to the MSDN message:

• Switch to the nearest window (Ctl + G)

• Perform the following statement:

  DBEngine.SetOption dbMaxLocksPerFile, 200000

---

Microsoft has a KnowledgeBase article that directly solves this problem and describes the reason:

The page locks required for the transaction exceed the MaxLocksPerFile value, which by default is 9500 locks. The MaxLocksPerFile parameter is stored in the Windows registry.

The KnowledgeBase article says it applies to Access 2002 and 2003, but it worked for me when changing a field in .mdb from Access 2013.

Answer 5

It is possible that in a database of this size you have text data that will not be converted to a valid date / time.

I would suggest (and you can hate me for that) that you export all these estimated date values ​​from "Big" and go through them (maybe in Excel) to see which ones are not formatted as you expected.

Answer 6

Assuming the error message is accurate, you are facing a disk or memory limit. Assuming your drive has more than a few gigabytes, I think restoring the table will cause the database (including workspace) to exceed the 2 gigabyte limit on the file in Access.

If you need the following:

• Upload the data in a convenient format and load it back into an empty database with an existing table definition.

• Move a subset of the data into a smaller table, change the data type in the smaller table, compact and restore the database, and repeat until all the data has been converted.

If the error message is NOT correct (which is possible), the most likely cause is a bad or out-of-date date in the text date column.

Answer 7

• Copy the table (ie, “YourTable”), then paste its structure back into your database with a different name (ie “YourTable_new”).

• Change the fields in the new table to what you want and save.

• Create an add request and copy all the data from the old table to the new one.

We hope that Access automatically converts the old text field directly to the correct value for the new Date / Time field. If not, you may need to clear the old table and re-add all the data and use the string to date function to convert this single field when adding.

Also, if there is an auto-dialing field in the old table, this may not work, because there is no way to guarantee that the old auto-dialing values ​​will match the new auto-dialing values ​​that will be assigned.

Answer 8

You were offered several ways to get around the error message on disk.

Have you tried adding a new field to your existing table using the Date data type, and then updating the field with the value of the existing row date field? If this works, you can delete the old field and rename the new one to the old name. This is likely to take less time than direct conversion from string to date in one field.

If it still does not work, you can do this using the sceond table with two columns, the first long integer (make it the primary key), the second is the date. Then add the PK field and date string to this empty table. Then add a new date field to the existing table and, using the join, update the new field with the values ​​from the two-column table.

This may run into the same problem. It depends on the number of things internal to the Jet / ACE database engine that we have no real control over.