Access front end Oracle back end 1

[rkinfo]

I have migrated my MS Access database/sql server to Oracle but front end is still MS Access. I have created ODBC Connection. I need to change modulesso that it points oracle database insted of pointing to ACCESS /SQL Server database. I have couple of

1) When should I prefer to use linked table vs ODBC link?
2) If I use linked table then my sql statement should be using ACCESS syntax or Oracle as they hace different variable type , select into Vs Insert/Create table
3) How to execute a statement from access on SQL Command in oracle
4) How to call oracle procedure from Access ?

Thanks in advance

 

[jrbarnett]

1. Linked tables are a type of link. ODBC is one data access technology that enables linked tables to work (the main other one related to Access is ADO). The two are not mutually exclusive.

2. If the processing is being done via an Access linked table, then the syntax should be done via Access/JET SQL; if they use pass through queries linked to Oracle, then you need to use Oracle syntax as it bypasses the Jet database engine that underlies Access.

3. Use the same methods as with Access:
DoCmd.RunSQL "SQL Statement"
DBEngine.Execute "SQL Statement"
or use a querydef object set to passthrough type using Oracle PL/SQL syntax.
4. See 3. Use the Oracle syntax to execute a stored procedure.

John

 

[rkinfo]

How to set up connection with oracle database using odbc connection

 

[jrbarnett]

Use the ODBC administrator and configure a data source using the Oracle driver appropriately for your server and database. The exact settings required will depend on how your Oracle is configured.

John

 

[rkinfo]

Will you provide me vb code as I have already configured odbc connection?

 

[jrbarnett]

Create the Linked tables in your Access database (New -> Linked Table -> choose your ODBC connection -> select tables).
Then use the examples in point 3 in my post of 8th Oct for running an action query, or use the Query wizard to generate select queries for reading data.

John

 

[rkinfo]

How to create connection in access module?