无法更改MS Access 2007上的数据类型 [英] Can't change data type on MS Access 2007

问题描述

我有一个巨大的数据库（800MB），它包含一个名为'上次修改日期'的字段，此时此字段输入为文本数据类型，但需要将其更改为日期/时间字段以执行一些查询。

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

我有另一个完全相同的数据库，但内部只有35MB的数据，当我改变数据类型它工作正常，但当我尝试改变数据类型大数据库它给我一个错误：

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

Micorosoft Office Access can't change the data type.

There isn't enough disk space or memory

一些网站提到的改变注册表文件（MaxLocksPerFile）也试过，但没有运气： - （

After doing some research some sites mentioned of changing the registry file (MaxLocksPerFile) tried that as well, but no luck :-(

任何人都可以帮助吗？

推荐答案

一个相对乏味的（但直截了当的）解决方案是将大数据库分解成更小的数据库，在更小的数据库上进行转换，然后重新组合它们。

One relatively tedious (but straightforward) solution would be to break the big database up into smaller databases, do the conversion on the smaller databases, and then recombine them.

这有一个额外的好处，如果某些情况下，文本在一个块中是无效的日期，将更容易找到（因为较小的块大小）。

This has an added benefit that if, by some chance, the text is an invalid date in one chunk, it will be easier to find (because of the smaller chunk sizes).

假设您在表格上有某种整数键，范围从1到（例如）10000000，您可以执行

Assuming you have some kind of integer key on the table that ranges from 1 to (say) 10000000, you can just do queries like

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

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

等。

单独输入并运行这些查询，因为如果您尝试一次运行多个，Access似乎会给您一个语法错误。

Make sure to enter and run these queries seperately, since it seems that Access will give you a syntax error if you try to run more than one at a time.

如果你的键是东西否则，你可以做同样的事情，但是你必须对你的WHERE子句有点棘手。

If your keys are something else, you can do the same kind of thing, but you'll have to be a bit more tricky about your WHERE clauses.

当然，最后要考虑的事情，如果可以摆动它，就是迁移到具有更多权力的不同数据库。我猜你有理由这并不容易，但是你正在谈论的数据量，你可能会遇到其他问题，以及你继续使用Access。

Of course, a final thing to consider, if you can swing it, is to migrate to a different database that has a little more power. I'm guessing you have reasons that this isn't easy, but with the amount of data you're talking about, you'll probably be running into other problems as well as you continue to use Access.

编辑

由于你还有一些麻烦，这里有一些细节希望你会看到我之前没有描述过的东西：

Since you are still having some troubles, here is some more detail in the hopes that you'll see something that I didn't describe well enough before:

在这里，你可以看到我创建了一个表OutputIDrive类似于你描述。我有一个ID标签，虽然我只有三个条目。
alt text http://www.freeimagehosting.net/uploads/2829dbd2d3.png

Here, you can see that I've created a table "OutputIDrive" similar to what you're describing. I have an ID tag, though I only have three entries. alt text http://www.freeimagehosting.net/uploads/2829dbd2d3.png

在这里，我创建了一个查询，进入SQL模式，并输入相应的SQL语句。在我的情况下，因为我的查询只抓取值> = 0和< 2，我们只得到一行... ID = 1的行。
alt text http://www.freeimagehosting.net/uploads/16e6e0dbc2.png

Here, I've created a query, gone into SQL mode, and entered the appropriate SQL statement. In my case, because my query only grabs value >= 0 and < 2, we'll just get one row...the one with ID = 1. alt text http://www.freeimagehosting.net/uploads/16e6e0dbc2.png

当我点击运行按钮，我得到一个弹出窗口，告诉/警告我会发生什么...它会把一排新的表。这是好的...这是我们正在寻找。我点击确定。
alt text http://www.freeimagehosting.net/uploads/319e58d763.png

When I click the run button, I get a popup that tells/warns me what's going to happen...it's going to put a row into a new table. That's good...that's what we're looking for. I click "OK". alt text http://www.freeimagehosting.net/uploads/319e58d763.png

现在我们的新表已经创建，当我点击它时，我们可以看到我们的ID = 1的一行数据已被复制到此新表格。

alt text http：//www.freeimagehosting。 net / uploads / f7fd0aad82.png

Now our new table has been created, and when I click on it, we can see that our one line of data with ID = 1 has been copied over to this new table.
alt text http://www.freeimagehosting.net/uploads/f7fd0aad82.png

现在，您应该只需修改SQL查询中的表名称和数字值，然后再次运行

Now you should be able to just modify the table name and the number values in your SQL query, and run it again.

希望这将帮助您解决问题。

Hopefully this will help you with whatever tripped you up.

strong>

EDIT 2:

Aha！这是诀窍。您必须在Access中一次输入和运行SQL语句。如果你试图把多个语句放入并运行它们，你会得到那个错误。所以运行第一个，然后删除它，运行第二个，等，你应该很好。我认为会做到的！我已编辑上述内容，使其更清楚。

Aha! This is the trick. You have to enter and run the SQL statements one at a time in Access. If you try to put multiple statements in and run them, you'll get that error. So run the first one, then erase it and run the second one, etc. and you should be fine. I think that will do it! I've edited the above to make it clearer.

这篇关于无法更改MS Access 2007上的数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！