Access和ODBC/Oracle都可以理解的通用SQL [英] Generic SQL that both Access and ODBC/Oracle can understand

问题描述

我有一个基于链接的ODBC表(Oracle)的MS Access查询.

I have a MS Access query that is based on a linked ODBC table (Oracle).

我正在对以下查询的不良性能进行故障排除:

I'm troubleshooting the poor performance of the query here: Access not properly translating TOP predicate to ODBC/Oracle SQL.

SELECT ri.*
FROM user1_road_insp AS ri
WHERE ri.insp_id = (
                    select 
                        top 1 ri2.insp_id   
                    from 
                        user1_road_insp ri2 
                    where 
                        ri2.road_id = ri.road_id 
                        and year(insp_date) between  [Enter a START year:] and [Enter a END year:]
                    order by 
                        ri2.insp_date desc, 
                        ri2.length desc,
                        ri2.insp_id
                   );

文档说:

发现问题时，可以尝试通过更改本地查询来解决问题.这通常很难成功完成，但是您可以 能够添加发送到服务器的条件，从而减少了 检索用于本地处理的行数. 在许多情况下，您会发现，尽管尽了最大的努力，Office Access仍然会不必要地检索某些整个表，并且 在本地执行最终查询处理.

When you spot a problem, you can try to resolve it by changing the local query. This is often difficult to do successfully, but you may be able to add criteria that are sent to the server, reducing the number of rows retrieved for local processing. In many cases you will find that, despite your best efforts, Office Access still retrieves some entire tables unnecessarily and performs final query processing locally.

但是，发生在我身上的是，我真的不明白我应该编写哪种SQL来使Access和ODBC/Oracle都满意.

However, it's occurred to me that I don't really understand what sort of SQL I should be writing to make both Access and ODBC/Oracle happy.

我应该写一些Access可以在本地查询 AND 中理解的 通用SQL ，并且可以轻松地将其转换为ODBC/Oracle SQL?通用SQL是真的吗?

Should I be writing some sort of generic SQL that Access can understand in a local query AND that can be easily translated to ODBC/Oracle SQL? Is generic SQL a real thing?

推荐答案

ODBC驱动程序使用哪种SQL?这通常取决于MS Access具有三种与之交互的外部数据连接类型.每种不同的SQL方言都使用ODBC API.

What kind of SQL does the ODBC driver use? It depends as typically MS Access has three types of external data connections that interfaces with different SQL dialects each with the ODBC API.

1. 链接表，其作用类似于本地表，但是是ODBC连接的数据源，并且不在本地存储.一旦将它们合并到Access应用程序中，这些表就只能使用MS Access的SQL方言.它们可以与其他来源的本地甚至其他后端表连接.

1. Linked tables that acts like local tables but are ODBC connected data sources and not stored locally. Once they are incorporated in an Access app, these tables can only use MS Access' SQL dialect. They can be joined with local or even other backend tables from other sources.

因此，为什么TOP在MS Access中可用，而在Oracle中不可用.您实际上是在使用Access SQL来操纵Oracle数据. ODBC用作数据的原始点，而Access的Jet/ACE SQL引擎在缓存中查看处理和结果集.

Hence, why TOP is available in MS Access and not Oracle. You are essentially using Access SQL to manipulate Oracle data. ODBC serves as the origin point of data while Access' Jet/ACE SQL engine does the processing and resultset viewing in cached memory.

传递查询，这些查询在本地应用程序环境中看不到本地表或其他任何内容.这样的查询使用连接的数据库的SQL方言(此处为Oracle).

Pass-through queries that do not see local tables or anything else in local app's environment. Such queries use the SQL dialect of the connected database here being Oracle.

因此，为什么TOP在Oracle中不可用，并且列标识符中允许使用双引号.这样的引用将在MS Access中失败.本质上，您正在使用Oracle SQL在Access应用程序中操作Oracle数据.您可以获取 sqlout.txt 日志的输出，并在通过ODBC连接到Oracle数据库的直通查询中运行它.

Hence, why TOP is NOT available in Oracle and double quotes are allowed in column identifiers. Such quoting would fail in MS Access. Essentially, you are using Oracle SQL to manipulate Oracle data in an Access app. You can take the output of the sqlout.txt log and run it in a pass-through query ODBC-connected to your Oracle database.

ADO/DAO记录集完全通过诸如VBA之类的代码运行，并且直接连接到数据源并使用连接数据库的方言.

ADO/DAO Recordsets that are run entirely via code such as VBA and are direct connections to data sources and uses the connecting database's dialect.

在这里，您使用Oracle SQL通过ODBC API在Access应用程序中操作Oracle数据.

Here, you using Oracle SQL to manipulate Oracle data in an Access app via the ODBC API.

在上述每种类型中，您都必须连接到后端ODBC数据源.您甚至不需要使用GUI，但可以使用Access的对象库来创建链接表(请参见.执行).

In each one of these types, you will have to connect to a backend ODBC data source. You do not even need to use the GUI but can use Access' object library to create linked tables (see DoCmd.TransferDatabase) and pass through querydefs (see QueryDef.Connect or .Execute).

我怀疑您看到的 sqlout.txt 日志是ODBC调用对其本地方言的翻译.

I suspect the sqlout.txt log you see are translations of the ODBC calls to its native dialect.

这篇关于Access和ODBC/Oracle都可以理解的通用SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！