ODBCLINK/SE Reference Manual (36217-90410)

ManualsBrandsHP ManualsSoftwareMPE/iX 6.0 Operating System

ODBCLink/SE Reference Manual Using ODBCLink/SE With ODBC Applications

ODBCLink/SE

©M.B. Foster Associates Limited 1995-2000 45

Notes for Users of MS-Access

⊗ For a table to be updatable in Access, it must have a unique key and Access

must know about it. If the table is defined in ALLBASE/SQL with a unique

key, this will be reported by ODBCLink/SE (in the SQLStatistics call that

Access makes) and the table will be updatable. If Access cannot locate a

primary key, it will prompt for the column or combination of columns that

make up a unique key. You can ignore this prompt, and the table will not be

updatable from Access; or you can specify one or more

columns that make a

unique key. If this combination of columns is not unique (i.e. if there are

duplicate records with the same key combination) Access will not work

properly. When you enter a unique key combination, ensure that it really is

unique within the table. To see the column or columns that Access uses as

the unique key, look at the table in

Design view. The column will have A

Akey@

symbol in the left most column.

⊗ Access uses a different algorithm to access a table depending on whether a

unique key exists or not. If it finds a unique key, it downloads the key values

and then issues an SQL statement of the form

Aselect...From...Where key=?

Or key=?...@

@. It then displays a screenful of data and stops. (Note, however,

that it may continue to download the keys in the background so long as the

table is open in

Atable view@

@.) If a unique key is not defined, Access just does

a regular Select statement to read the table. It will display a screenful of data

but will still continue to read the table in the background. You may view the

SQL that Access (and any ODBC application) generates by turning on

logging in the ODBC Setup Screen .

⊗ To update a record in Access, you highlight the column you want to change,

make the change, and then click on any other record. Access uses an

algorithm called

AOptimistic concurrency control@

@ to verify that a record has

not been changed by another user. This is a less secure method than the

SELECT FOR UPDATE used by other applications, however it minimizes

the number of locks that are held on the database. Optimistic concurrency

control works by Access generating and Update statement of the form

AUpdate Table Set column1=?, column2=?,...WHERE column1=? AND

column2=? AND column3=? And ....

@. This ensures that the update will fail

if the record has been changed by another user since the time it was last read

in by Access.

•The default type of join used by Access is the Inner Join. When

creating a Query with a join between two tables, you can change this to

a Left Outer Join by double-clicking on the link between the tables. You

may also use the Edit Joins screen to change the default join between

specific tables, so that you don

=t have to edit the join every time you

create a new Query.

⊗ You cannot join a local table to a remote table efficiently in Access unless

there is a one-to-one correspondence between records in the local table and

records in the remote table; in other words, you must have all the columns in

the local table that are necessary to form a unique key on the remote table. If

this is not the case, Access will attempt to download the remote table to the

local machine.