Re: How do I set up Access/Windows to 'access' Oracle db on Netware

From: Jim Danforth <jimdan_at_interserv.com>
Date: 1996/10/23
Message-ID: <01bbc0f6$e4edd420$32a1aec7_at_USR2.interserv.com>


Read page 221 of "building applications for Microsoft Access for Win 95 This should help

        excerpt from Access help file follows:

Data Access applications can connect to any database with an ODBC driver. Before any application can access an ODBC database, the ODBC drivers must be installed and a Data Source Name (DSN) created using the Windows Control Panel or the RegisterDatabase method. Whenever you refer to an ODBC database, this DSN is used. It contains the linkage information used by the ODBC drivers and the Microsoft Jet database engine to establish a connection to the database.

Once you have installed the proper drivers and created a DSN, you can use the DSN to connect to an external ODBC-based table. There are two ways to connect to ODBC database tables:

Link an ODBC database table or view to a Microsoft Jet database and use the OpenRecordset method to access the table.

        Create a QueryDef object that references an ODBC database.

The most efficient way to access ODBC database tables is to link the external table to an existing Jet database. Linking a table creates a reference to the external table and saves it in your Jet database. This stored link keeps a record of the database table's structure. None of the external database data is stored in the .mdb file. You can create dynaset-type and snapshot-type Recordset objects with linked tables.

Linking ODBC tables to an existing Jet database

To link an ODBC-based table to your database, you can write code as outlined in the following steps:

  1. Create variables for the Database and TableDef objects you are going to modify.

Dim tdf As TableDef, dbs As Database

2. Use the OpenDatabase method to open the existing database.

Set dbs = OpenDatabase("C:\Database\Mydb.mdb")

3. Create a TableDef object for the external table.

Set tdf = dbs.CreateTableDef("Customer Detail")

         4. Set the TableDef object properties to refer to the linked table.

tdf.Attributes = dbAttachedODBC
tdf.Connect = "ODBC;DSN=SSRVR1;UID=Fred;PWD=RHS;DATABASE=SQLDB;"
tdf.SourceTableName = "CustDetail"

5.	Append the TableDef object to the TableDefs collection with the Append
method. This step actually creates the object links in the database file.

Creating a QueryDef object that references an ODBC database

Although linking is the most efficient way to access data stored in ODBC tables, it does not support SQL pass through queries. In cases where you need to execute SQL statements that can only be executed on the ODBC database, like Transact SQL statements on Microsoft SQL Server, or to execute stored procedures, create a QueryDef object whose Connect property contains an ODBC connect string. The Jet database engine can return multiple result sets and messages from stored procedures when you use a QueryDef object to execute the procedure.

When using ODBC databases, the LockEdits property is always False. The Jet database engine defaults to optimistic locking. You can update ODBC database tables only if a unique key exists for the table. If you need to use and update an external temporary table, create an index for it before linking it.
You can set an upper limit on the time that the Jet database engine takes to open an ODBC database by setting the LOGINTIMEOUT argument in the Connect string. You can also limit the time the Jet database engine takes to execute an ODBC query by setting the QueryTimeout property of the Database object or the ODBCTimeout property of the QueryDef object. When any of these preset times expire, a trappable error occurs.

To perform many of its operations more quickly on Microsoft SQL Server systems, the ODBC driver may create one or more stored procedures in your working database. If your program terminates abnormally, these procedures may remain on the server. Generally, it is safe to remove these procedures manually once you determine they are not being used.

For more information on SQL pass through queries, refer to Using SQL PassThrough.

Note adjust the parameters used by the Jet database engine when working with ODBC databases, create a special table, MSysConf, on each ODBC database that you expect to access from your Jet database engine application. For more information, please refer to Using the MSysConf Table with SQL Databases.

bradspie_at_SoCA.com wrote in article <325FF794.1F98_at_SoCA.com>...
> Hi:
>
> If someone could point me to a document that explains the steps
> needed to set up MS Access/Windows so that it can get to an Oracle
> database on a NetWare 3.1* server I would be most grateful.
>
> I have some experience with Access, but none with Oracle or
> NetWare setups. I am trying to use MS Access to link to an Oracle
> database on the server, but haven't a clue as to how to proceeed.
>
> I have one PC set up with a two-user NetWare 3.1* server and
> Oracle installed. The second PC has Windows 3.11 with Access
> installed as well as the 90 day trial version of Oracle. Using File
> Manager I can access the files in the Oracle sub-directory
> on the NetWare server.
>
> When I use the Oracle/Windows utilities I can connect to
> the sample DB that was installed on the Windows PC. How do I
> set up Access/Windows/Oracle/NetWare to allow me to do what I
> want?
>
> HELP!
>
> Robert W. Bradspies -- bradspie_at_SoCa.com
>
Received on Wed Oct 23 1996 - 00:00:00 CEST

Original text of this message