|
Category :
Client
Applications
DB Version
:
Oracle 8i / 9i
OS Details :
Windows NT / 2000 / XP
One
of the methods used by
MS-ACCESS to query oracle tables is by linking the tables and then
issuing queries against it.
In this article we'll
look at querying oracle without the linking of the tables.
Conditional column selects can be done in MS-ACCESS using a method
called SQL-pass through query. Listed below are the steps that need to
be followed to do pass-through queries:
Step 1 :
- Create a new "module"
within MS-ACCESS. (Get to module tab and click new).
- Add the following
function to the window :
Function CreateSPT(SPTQueryName As String, SQLString As String, _
ConnectString As String)
'-----------------------------------------------
' FUNCTION: CreateSPT()
' PURPOSE:
' Creates an SQL pass-through query using the supplied arguments:
' SPTQueryName: the name of the query to create
' SQLString: the query's SQL string
' ConnectString: the ODBC connect string, this must be at
' least "ODBC;"
'-----------------------------------------------
Dim mydatabase As Database, myquerydef As QueryDef
Set mydatabase = DBEngine.Workspaces(0).Databases(0)
Set myquerydef = mydatabase.CreateQueryDef(SPTQueryName)
myquerydef.Connect = ConnectString
myquerydef.SQL = SQLString
myquerydef.Close
End Function
Step 2 :
- From within the module
window, hit save and then hit the "debug window" button.
- Once inside the debug
window, type the following :
? CreateSPT("Test1",
"Select col1, col2, col3, col4 from test_table","ODBC;DSN=DEVDB;Database=DEVDB;USID=testuser;PWD=testuser_dev")
- Highlight the above
statement and hit enter key once. This will create the query called
test1.
- Close the debug window
Step 3:
- Make sure that
an ODBC data source with the name used in step2 exists on the machine
from which the query will be run.
Step 4 :
- Get to the QUERIES tab
and double-click on the query name to run it.
|
