英文:
ms-access SQL linked sheet row order retention
问题
在从链接表派生的表中保证行顺序是否有方法？在循环遍历多个工作表和工作簿时，我似乎偶尔会失去行顺序（这不幸地具有意义）。
DoCmd.TransferSpreadsheet acLink, , "linked_sheet ", sfile, False, "some_sheet!A1:U100"
CurrentDb().Execute "'&" & identifier & "' as id1, " & identifier2 & " as id2, i.* into temp_table from linked_sheet i"
CurrentDb().Execute "alter temp_table add column ky counter primary key"
密钥是正确的顺序的，但行不同于原始电子表格。
问题是间歇性的，并且不会在每次出现在相同的工作表/工作簿上。
如果我捕捉到错误，然后返回并重新运行相同的代码，它将返回与电子表格中显示的原始行顺序相同的正确行顺序。
行不会丢失，它们会移动到temp_table中的不同位置。
我在查询linked_sheet时将工作簿保存在内存中，这可能会导致问题吗？
英文:
Is there a way to guarantee row order in a table derived from a linked sheet? I seem to occasionally lose row order (which unfortunately has meaning) when I select from a Linked Sheet while looping over a number of sheets & WB.
DoCmd.TransferSpreadsheet acLink, , "linked_sheet ", sfile, False, "some_sheet!A1:U100"
CurrentDb().Execute "select '" & identifier & "' as id1, " & identifier2 & " as id2, i.* into temp_table from linked_sheet i"
CurrentDb().Execute "alter temp_table add column ky counter primary key"
The keys are correctly ordinal, but the rows are not the same as the original spreadsheet.
The problem is intermittent and does not occur on the same Sheet/WB every time.
If I catch the error, then go back and rerun the same code, it will return correct original row order as it appears in the sheet.
The rows are not missing, they are moved to a different location in temp_table.
I'm holding the WB in memory while querying the linked_sheet could this be causing the problem?
答案1
得分: 2
To wax philosophically, you should not care about the order of rows inserted into the target table. That is at the db engine's discretion. The only time you should be concerned is upon presentation, when you select from the temp table. This is the most likely reason that "order by" is not supported by this syntax.
May I inquire why it is an issue?
Other databases use a syntax "insert into x from (select * from y order by z)", where the "order by" controls the order in which the rows are presented to the insert statement. But it still remains that the order of data in the db should not be relevant.
英文:
To wax philosophically, you should not care about the order of rows inserted into the target table. That is at the db engine's discretion. The only time you should be concerned is upon presentation, when you select from the temp table. This is the most likely reason that "order by" is not supported by this syntax.
May I inquire why it is an issue?
Other databases use a syntax "insert into x from (select * from y order by z)", where the "order by" controls the order in which the rows are presented to the insert statement. But it still remains that the order of data in the db should not be relevant.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论