将 Access 数据库转换为 SQL
目前我正在执行从 microsoft access 数据库到 SQL Express 2010 数据库的迁移。
基本上，我有一个用于搜索客户数据库的 Access 应用程序。 Access 应用程序分为两部分开发。每个客户端上的访问前端称为 application.mdb，Windows 2008 服务器上的数据后端称为 data.mdb。 application.mdb 有 3 个链接到 data.mdb 的表。它保存着客户、合同和物品。客户表与合同表相关（一对多），合同表与项目表相关（一对多）
我以相同的名称将表从 data.mdb 导入到 sql 表中并创建了相同的关系将它们配置为级联。然后，我在客户端上创建了一个 obdc 连接，并更新了 application.mdb 中的 3 个链接表以指向 sql 服务器上的表。
我启动应用程序，一切似乎都运行良好，我可以完美地看到所有数据，并且性能的提高非常值得付出努力。
然后我发现一个问题，当我向数据库添加新客户时，它会对客户表和合同表自动编号，但不会对项目表进行自动编号......因此，如果我尝试为新客户更改项目表中的任何项目我不能。我收到以下错误“无法添加记录；表“项目”的主键不在记录集中”，这是有道理的，因为 SQL 没有对项目表进行自动编号。
我不明白为什么......
任何帮助将不胜感激。
Currently I'm performing a migration from a microsoft access database to an SQL Express 2010 database.
Basically, I have an Access application that searches a customer database. The access app is developed in 2 parts. An access front end on each client called application.mdb and a data backend on a windows 2008 server called data.mdb. The application.mdb has 3 linked tables to data.mdb. which holds customers and contracts and items. The customer table relates to the contracts table (one to many) and the contracts table relates to the items table (one to many)
I imported the tables from the data.mdb into the sql tables by the same name and created the same relationships and configured them to cascade. I then created an obdc connection on the clients and updated the 3 linked tables in application.mdb to point to the tables on the sql server.
I start the application and everything seemed to work great, I can see all the data perfectly and the performance increase was well worth the effort.
Then I found a problem, when I add a new customer to the database it autonumbers the customer table and the contracts table but not the items table.... Thus if I attempt to alters any of the items in the items table for new customers I can not. I get the following error "cannot add record(s); primary key for table "items" not in recordset" which makes sense because SQL had not autonumbered the items table.
I can't understand why....
Any help would be greatly appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
好吧，只需在项目视图中直接手动添加记录就可以告诉您自动编号是否正常工作。当您在直接表视图中编辑和使用时，必须使自动编号正常工作。
与往常一样，此类问题归结为细节。与访问应用程序相比，使用基于 SQL 的后端时的不同之处在于，在实际保存记录之前，基于服务器的系统不会生成自动编号（主键）。当使用基于喷射的后端时，一旦记录被弄脏，自动编号就可用。
因此，我会检查应用程序中是否运行了某种类型的代码或事件，这些代码或事件在实际保存记录之前尝试用作主键值。
通常访问会做得很好。例如，当您在access中构建一个表单，然后在access中创建一个子表单来编辑子记录（和子表）时，那么通常当焦点从主表单切换到子表单时，access将强制执行保存主记录。因此，这意味着主键（自动编号列）现在可用于关系的正确运行。 Access 可以并且将会使用该 PK 值，并将该值插入到该子表的外键值列中。
但是，只有当您在子表单控件中正确设置链接主控和链接子级设置时，访问才会为您执行上述操作。作为在常规访问中构建表单时的一般规则，Access 可以检测所需的设置，并将正确的值插入到链接主设置和链接子设置中。但是，链接表不会检测 FK 列。
因此，当您使用SQL Server时，您必须在子表单控件中手动编辑和设置这些值。因此，我会检查您用于编辑此数据的子表单中的链接主设置和链接子设置，并确保设置了正确的值。如果这是 VBA 代码，请确保在尝试使用和获取 PK 值之前实际保存了记录。
我应该指出，即使在非基于 SQL Server 的应用程序中，在子表单中设置链接主 + 子设置也允许您访问设置和维护此外键值。因此，Access 始终能够为您插入这些值，并且您无需编写任何代码即可完成此操作。因此，在插入和维护这些值的编辑过程中，Access 会为您完成所有工作（因此，不是数据引擎为您插入这些 FK 值，而是用户界面或在某些情况下您编写的代码），
因此 access 不会设置并插入这些正确的值，除非您在该子表单控件中设置链接主+子设置。
我只是检查您在此处使用的任何子表单控件中的链接主控和子主控设置是否正确。
Well, just manually adding record direct in the items view should tell you if the autonumber is working. You MUST get the autonumber working when you edit + use in direct table view.
As always these kinds of issues comes down to the details. One thing that's different when using a SQL based backend compared for access applications is the generation of auto numbers (primary key) does not occur on server based systems until record is actually saved. When working with the jet based back end, the auto number is available the instant the record is dirtied.
So I would check if you have some type of code or event running in the application that is attempting to use as primary key value before the record been actually saved.
Usually access does a pretty good job. For example when you build a form in access, and then have a sub form in access to edit child records (and a child table), then as a rule when the focus switches from a main form to a sub form, access will force a save of the main record. This thus means the primary key (auto number column) is now available for correct functioning of the relationship. Access can and will use this PK value and insert this value into the foreign key value column in this child table for you.
However access will only do above for you WHEN you correctly set up the link master and link child settings in the sub form control. As a general rule when building forms in regular access, Access can detect the settings required and insert the correct values into the link master and link child settings for you. However, the detection of the FK column will not occur with linked tables.
So when you use SQL server, you have to edit and set these values manually in the sub form control. So I would check your link master and link child settings in the sub form you're using to edit this data, and ensure that the correct values are set. If this is VBA code, then ensure the record is actually saved before attempt to use and grab a PK value.
I should point out that even in non SQL server based applications, it is the setting up of the link master + child settings in the sub form that allows access to setup and maintain this foreign key value for you. So access is always had the ability to insert these values for you, and it'll do so with you about having to write any code at all. So during the editing process to insert and maintain these values Access does all of the work for you (so it's not the data engine that inserts these FK values for you, but the user interface or in some cases code you write)
So access will not setup and insert these correct values unless you set up the link master + child settings in that sub form control.
I would simply check if your link master and child master settings are correct in any sub form control you are using here.
这听起来像是一个愚蠢的答案，但请检查项目表以确保自动编号已打开。
This sounds like a stupid answer but check the Items table to be sure that auto-numbering is turned on.
每当您将 Jet/ACE 数据库迁移到 SQL Server 时，我建议的一件事是彻底检查数据库设计，例如：键和约束的实现、数据类型的选择、索引的选择等。Jet/ACE 是这与大多数 SQL DBMS 非常不同，因此您不应该假设适用于 Jet/ACE 的数据库设计就自动适合 SQL DBMS。升迁向导并不总能识别所有可能的问题。
在 SQL Server 中，最接近“自动编号”的等效项是 IDENTITY 属性。检查以确保表中哪些列是 IDENTITY，如果需要，请创建一个 IDENTITY 列。
One of the things I would suggest whenever you migrate a Jet/ACE database to SQL Server is to thoroughly review the database design, e.g.: the implementation of keys and constraints, choice of data types, choice of indexes, etc. Jet/ACE is a very different thing to most SQL DBMSs so you shouldn't assume that a database design that worked well for Jet/ACE is automatically suitable for a SQL DBMS. Upsizing wizards won't always identify every possible issue.
In SQL Server the nearest equivalent of an "auto-number" is the IDENTITY property. Check to be sure which columns are IDENTITY in your tables and create an IDENTITY column if you need one.