Generic SQL That Both Access and Odbc/Oracle Can Understand

Generic SQL that both Access and ODBC/Oracle can understand

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. 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.

   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.

   

2. 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.

   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.

   

3. 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.

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

   

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).

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

Access VBA to Import Oracle Table: ODBC--Call Failed

I was able to solve this error. Just copy the below code into your error handler and it will give you a more detailed description of the ODBC--Call Failed error

'DAO Error Handler
Dim MyError As Error
Debug.print Errors.Count
For Each MyError In DBEngine.Errors
With MyError
MsgBox .Number & " " & .Description
End With
Next MyError

This told me that my ODBC connection was being timed out due to the large size of the SQL query. Just follow the steps in the link below to resolve the error.

http://its.uiowa.edu/support/article/101855

Related Topics