在Access中切换字段的数据类型是否存在危险？ [英] Is there a danger in switching the data type of a field in Access?

问题描述

我正在使用由我公司维护多年的非常大的Access数据库。由于其中一个表中的字段被编码为文本字段而发生了麻烦，当我认为它应该是一个数字字段时（这将阻止发生小的麻烦）。如果更改此字段，数据库是否有任何危险？此表是一个主表，链接到许多查询，表单和报表，因此我不想更改此字段的数据类型，如果它可能导致任何其他查询，报表或表单中的错误。


是否有理由将此字段编码为文本而不是我可能不知道的数字？受影响字段中的值范围为0到大约30,000，并且仅为整数。


我意识到这可能是一个模糊的问题，可能不容易回答，但是，我会感激任何反馈。

I''m working with a very large Access database that has been maintained by my company for many years. There are nuisances that occur due to a field in one of the tables being coded as a text field, when I believe it should be a number field (this will stop the small nuisances from occurring). Is there any danger to the database if this field is changed? This table is a master table that is linked to many queries, forms and reports so I don''t want to alter the data type for this field if it could potentially cause errors in any of the other queries, reports or forms down the line.

Is there a reason this field may have been coded as text instead of a number that I could be unaware of? The values in the affected field range from 0 to around 30,000 and are whole numbers only.

I realize this may be an obscure question and may not be easily answered, however, I''d appreciate any feedback.

推荐答案

首先备份数据库并制作表的副本。接下来，使用文本字段检查查询以进行连接。如果找不到，请创建一个新的数字字段以保存文本字段中转换的数字值。如果您确实根据文本字段找到了联接，则必须将它们连接到另一个文本字段，否则会出现数据错误类型错误。这可能会变得很困难，但您需要弄清楚如何将数字字段连接到查询的另一个表中的数字字段，或者将查询中另一个表中的连接字段更改为数字数据类型。接下来，检查以确保文本字段中的所有值都是数字。运行查询，使用IsNumeric（）函数查找任何非数字值。然后，在解析文本字段中的所有非数字值后，将数字字段更新为CLNG（TextField）。最后，重命名文本字段并将数字字段重命名为文本字段的先前名称。祝你好运。

First back up the database and make a copy of the table. Next, check queries using the text field for joins. If you find none, create a new numeric field to hold the converted number value from your text field. If you do find joins based on the text field, they must be joined to another text field or there would be a data mistype error. This could turn out to be difficult, but you''ll need to figure out how to join your numeric field to a numeric field in the other table of the query or else change the join field in the other table in the query to a numeric datatype. Next, check to make sure all values in your text field are numeric. Run a query where you use the IsNumeric() function to find any non-numeric values. Then, update your numeric field to CLNG(TextField) after you resolve all non-numeric values in the text field. Finally, rename your text field and rename your numeric field to the previous name of your text field. Good luck.

备份文件！

我可以没有足够的。

因为我们不知道原始文件在其下开发的模式很难说。但是，最有可能的原因是默认字段类型是文本，原始开发人员只使用默认值。


最有可能需要对您的查询进行调整。由于该字段为文本，因此您将在搜索字词周围添加引号。必须删除这些数字字段。


您还必须查看表连接...依赖于text的所有其他表字段数字的形式也必须是delt ...而且可以是熊。


实际上只有一种方法可以找出会破坏的东西...... br />

备份后再复制一份。这将是您的开发副本。所以现在你有了生产文件，备份文件和生产文件。


如果这是一个拆分数据库（你没有说明这一点），那就进行相同的备份和复制对于每个文件。


现在把您的生产副本放在PC /网络上一个易于访问的位置...如果需要，重新链接任何链接的表格并查看如果它正常工作。


现在进行更改。


测试数据库，修复任何问题，然后重新测试。一旦按预期工作，您将需要弄清楚如何将它们应用到您的生产副本。


对于表单，报告和查询，您可以复制这些对象。对于桌子，您需要手动更改。


告诉我们您是如何获得的。



备份

备份！

备份!!!

BACK UP THE FILE!
I can not stess that enough.
Because we do not know the schema that your original file was developed under it is hard to say. However, most likely because the default field type is text your original developer simply used the default.

There most likely will need to be adjustments made to your queries. Because the field is text you will have quote marks around the search terms. These will have to be removed for numeric fields.

You will also have to look at table joins... all the other table fields that rely on that "text" form of the numeric will have to be delt with too... and that can be a bear.

There''s really only one way to findout what will break...

Once you have a back up... make one more copy. This will be your development copy. So now you have, the production file, the backup file, and the production file.

If this is a split database (you do not indicate this) then make those same backups and copies for each file.

Now take your production copy, put it in an easy to access location on your PC/Network... re-link any linked tables if needed and check to see if it works correctly.

Now make your change.

Test the database, fix any issues, and retest. Once this works as expected you will need to figure out how to impliment them to your production copy.

For forms, reports, and queries you can copy those objects over. For the table you will need to do that change by hand.

let us know how you get a along.


BACK UP
BACK UP!
BACK UP!!!

有没有快速简便的方法来检查连接？对我来说，只有太多的查询要逐一查看。

Is there a quick and easy way to check for joins? There are simply too many queries for me to check through them one by one.

这篇关于在Access中切换字段的数据类型是否存在危险？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！